Macro - Splitting Sentences In 2 Columns?

Apr 29, 2014

i need some macro code to split a sentences become 2 string/text, like this below :

assuming start data in col.a

sample raw (col.a)
after macro (splitting 2 text)
The Internet and media are closely controlled in China
The Internet and media are closely controlled in
China

The crackdown hit Chinese Internet stocks
The crackdown hit Chinese Internet
stocks

You can feel the pressure in Doha
You can feel the pressure in
Doha

It exploits data left in memory after an operation
It exploits data left in memory after an
operation

etc..

it's possible splitting a sentence become 2 string/text in 2 column (col.b & col.c as desired result). in col.c result based on last word a sentences..​

View 7 Replies


ADVERTISEMENT

Macro To Manipulate Article Sentences

Oct 9, 2009

I'm trying to write a macro that will take 5 articles that I have written and separate the sentences out....

So what I need to happen is I take the articles and pop them into excel, then the macro will pick the first sentence of the first article, then the first sentence of the second article, then the first sentence of the third article....and basically repeat this for all the sentences in all the articles also while adding a "|" in between the sentences and adding a starting "{" and an ending "}" for each of the first sentences, second sentences and so on.....

I don't know that I'm making any sence here so here is a very small example of what I need to happen....

Article 1 Paragraph 1: I like blue. I like Green. I like Purple.
Article 1 Paragraph 2: I like flowers. I like dasies. I like tulips.

Article 2 Paragraph 1: I like football. I like basketball. I like soccer.
Article 2 Paragraph 2: I like food. I like wine. I like beer.

Article 3 Paragraph 1: The sky is blue. The sky is dark. The sky is night.
Article 3 Paragraph 2: I love stars. The moon is big. The moon is full.

So each of articles would need to be placed in separate sheets I'm guessing?

Below is what I need the text to look like when the macro has finished.....

Spun Article Paragraph 1:

{I like blue.|I like football.|The sky is blue.}{I like Green.|I like basketball.|The sky is dark.}{I like Purple.|I like soccer.|The sky is night.}

Spun Article Paragraph 2:

{I like flowers.|I like food.|I love stars.}{I like dasies.|I like wine.|The moon is big.}{I like tulips.|I like beer.|The moon is full.}

I hope this all makes sense.....

I already set this up using the record macro feature of excel and it works ok but I end up having to do a lot of editing because it will put to many brackets in or not enough so I was hoping for some advise or possibly some code example that could get me headed in the right direction....

View 10 Replies View Related

Splitting Address Data From 2 Different Columns Into City And State Columns?

Dec 5, 2012

I have been researching this for 3 days and I cannot find a solution. I have City, State information in A1. I also have City, State information in B1. I need to put them into City (D1) and State (E1).

View 4 Replies View Related

Splitting One Column Into Many Other Columns

Dec 22, 2007

I need a macro which place chosen parts from one column into another column.

For example I have following text in column E:

Column E
Flugschneise {f}
ringsum
(total) im Arsch [vulg.]
mutterseelenallein
Geld und Gut
Pensum {n}

*chosen content not always at the end of a cell!

Now i need a macro which puts chosen content from Column E to Column F or Column G. For example I want that all {f}s should be deleted from column E and put into column E. Or I want all {n} to be deleted and placed into column F. Or I want all [vulg.] to be deleted from column E and put into column G. If column F or G already consist of text then the new text( {f} or {n} or [vulg.] ...) should be added to the existing text but separated through a separator like comma or semicolon

It means that the macro is each time fed with information about what to delete from which column and where to put it. It would be cool if a simple msg box would ask for the needed information.

View 14 Replies View Related

Splitting Names Into 2 Separate Columns

Apr 11, 2008

I have two columns in my excel sheet, the first column has the Second name, First Name and the second column has the Date of birth.

Now what I'm after is two columns really, the first column should show the Furst name Second Name (space in between with no Comma) and the Second column should show the First Initial from the first name, the first initial from the second name and then the date of birth.

View 11 Replies View Related

Splitting One Row With Two Pieces Of Data Into Two Columns With Same Name

Jan 29, 2013

I have a homework assignment where I have a list of companies with the products they have with one other company. The original company and their products are listed on the same row, with a dollar value assigned to the product. So Cell A1 has company name, cell B1 has product a, and Cell c1 has the amount they pay for that product. Is there a function I can use to split that one row into 3 separate rows, to show the company name 3 times and the product and dollar amount as it's own row, next to the company?

View 1 Replies View Related

Splitting Address Details Into Several Columns

Oct 18, 2006

I'm in need to split 1 column (address details) into several columns (for ex. address, city, postcode, country) I can't use the "Text to Column" function as some address will split into 5 part, some split into 6/7/8. Is there any way I could do this automatically as there're a lot of entries in my spreadsheet. Example of the data need to be split attached.

View 5 Replies View Related

Splitting A List Of Values In Separate Columns?

May 8, 2014

I am trying to put something together on Excel.

1. In my first column(Let's call it "input"), there will be a variety of values from 1-10 which will appear in cells as 1,7,10 or 4,5,6,7 or any other similar variation. The quantity of numbers can vary and will always be comma delimited.

2. Following the first column are 10 additional columns. For simplicity, let's say they're labelled 1-10.

3. If 2,5,6,9 is present in the first cell, I want the number 1 in the columns labelled 2,5,6 and 9.
If 1,4 is present in the first cell, I want the number 1 in the columns labelled 1 and 4.

The input will only be in the first cell so I am hoping to find a way to automate the placement of 1's in the appropriate column depending on the input in the first column("input").

As a bonus, I'd like all cells not present in the first column to appear as a "0".

View 14 Replies View Related

Splitting Numbers And Text To Their Respective Columns

Jul 31, 2014

Splitting numbers and text separately to their respective columns

Annexed file for reference: SPLIT TEXT AND NUMBERS.xls‎

View 7 Replies View Related

Splitting Cells With A Formula And Not Use Text To Columns

Aug 13, 2014

I want to split a cell with a formula and not use text to columns. I will have a cell a1 for example with 1d 4h 36m 34s in and want to have 4 formulas 1 in
a5
a6
a7
a8

.. so the result will be
1
4
36
34

I know you can split with left, mid and right but don't know how to do it with the desired results.

View 7 Replies View Related

Splitting Comma Delimited Numbers Into New Columns

Apr 20, 2009

I am using excel at the moment with a card playing program. using the excel sheet they provided the details of what cards are dealt are exported to the worksheet and there is a simple table like so

Player Cards
............................................................
Player 1/ 24, 27, 16
Player2/ 1, 5

The information is fed through one number at a time as the cards are dealt for a total of three rounds sometimes it is only two rounds and are delimited by a comma all in the same column. I would like if possible to have these numbers appear in separate columns. that is

Card 1 / Cards 2 / Card 3
Player 1
Player 2

IS this possible. briefly i want this to happen so I can use the Vlookup function as the numbers that come through each stand for a card value but using Vlookup only the first number works and the following return an NA value as it is impossible as far as I know to have every possible combination represented in a table . If there is a way of tweaking Vlookup so it recognises the comma delimiter and in the vlookup column it will show all converted numbers then i'm all ears otherwise any help on how to split would be much appreciated. Quickly I did try using the text to columns function when i did this however in the new destination it showed only the first number and discontinued showing the others in the original as well. Additionally in this function the 'preview of selected data' does not show selected data but some sort of link =programme_name_card_gamecard_1 somethig like that. Sorry for the long one.

View 12 Replies View Related

Splitting Text And Numbers From 1 Column Into Separate Columns

Jul 24, 2014

I have some data that is both text and numbers in the same cell. I would like to split the the data so that the text is in one column and the numbers are in another column.

The numbers are all a fixed length (15 chars) so I know that I can use the following formula = RIGHT(A1,15)

However I am not quite sure how to split the text as the length can vary as as well as the number of words in the string.

For example A1 is 1 word with 7 characters but A2 is 2 words, 14 characters long inc space.

A1 Goodwin 000710280740120
A2 Gillette Ridge 000715058510122

B1 Goodwin
C1 000710280740120

B2 Gillette Ridge
C2 000715058510122

View 4 Replies View Related

Splitting Text String (address) Into Separate Columns

Jun 2, 2014

I have address that sits in one cell only and I need to separate into two or three columns (streetname, street number, streetletter).

Is this possible as the length of street text, numbers and letter differs all the time...

Attached is a sample file with before and after result

address.xls‎

View 5 Replies View Related

Splitting A Long Column Of Data Into Separate Columns

May 21, 2008

i have a long column pair of data, each entry in its own cell:

10/5/20088:30:00 AM
10/5/20088:46:00 AM
10/5/20089:14:00 AM
10/5/200810:18:00 AM
10/5/200810:42:00 AM
11/5/20088:30:00 AM
11/5/20088:46:00 AM
11/5/20089:14:00 AM
11/5/200810:18:00 AM
11/5/200810:42:00 AM
12/5/20088:30:00 AM
12/5/20088:46:00 AM
12/5/20089:14:00 AM
12/5/200810:18:00 AM
12/5/200810:42:00 AM
13/5/20088:30:00 AM
13/5/20088:46:00 AM
13/5/20089:14:00 AM
13/5/200810:18:00 AM
13/5/200810:42:00 AM
14/5/20088:30:00 AM
14/5/20088:46:00 AM
14/5/20089:14:00 AM
14/5/200810:18:00 AM
14/5/200810:42:00 AM

how can i program a macro to 'split' this column according to date? please refer to the attached picture as an example. i know this is probably a simple question but please bear with me i'm still new to excel programming.

View 14 Replies View Related

Text Functions To Add X Character In Between Sentences

Apr 6, 2014

I have thousands of track titles into my music library that requires some organization. In this case below, I need to add a "" character to split the track number from its title. So, the problem remains at the lack of pattern in the text and the only reference it the beginning of the song title (but it also can begin with a text or a number).Text.jpg

From: 01 - It's A Long Way To The Top.mp3 >>>>>>>>>> To: 01It's A Long Way To The Top.mp3
From: 02-Crazy.mp3 >>>>>>>>>> To: 02Crazy.mp3
From: 3-01 Need Your Love.m4a >>>>>>>>>> To: 3-01Need Your Love.m4a
From: 10-13 Angels.mp3 >>>>>>>>>> To: 10-13Angels.mp3
From: 01 10_15 Saturday Night.m4a >>>>>>>>>> To: 0110:15 Saturday Night.m4a
From: 3 - Guitar Mafia.mp3 >>>>>>>>>> To: 3Guitar Mafia.mp3
From: 1-01 Losing all.m4a >>>>>>>>>> To: 1-01Losing All My Friends.m4a

View 12 Replies View Related

Inserting Percentage Value Into Sentences In Excel?

Jul 23, 2014

inserting a percentage value from two cells into a sentences in excel.

A1 = 822,394,133 (CashReceived)
A2 = 126,103,052 (ARReceived)

Here's my formula: =”Organization represented approximately "&TEXT(ARReceived/CashReceived,”0%”)&" of the cash receipts.”

The TEXT function not working on the division formula. I tried the CONCATENATE, not working either.

View 4 Replies View Related

VLookup - Find Words In Sentences

May 30, 2014

The idea is try to search the words from Column C in Column A and if some key is found, show value from Column C (As a simple Vlookup but column A contains sentences):

A B C D

This is a test test 1
This is a tst excel 2
test case 3 tst 3
case for excel
test 4
tst nº 5

Excel1.JPG

And expected result should be: (column B, contains the formula):

A B C D

This is a test 1 test 1
This is a tst 3 excel 2
test case 3 1 tst 3
case for excel 2
test 4 1
tst nº 5 3

Excel2.JPG

The idea is not to use. Column C could have more of 1000 entries.

View 1 Replies View Related

Convert Uppercase Sentences To Sentence Format

Jul 19, 2007

I need to find a formula that will allow me to format an upperase sentance to sentance format.

I.E. "UPPERCASE SENTENCES SHOULD NO BE FORMATED USING THE ' PROPER' FORMULA."

I need the sentace to format like this:

"Uppercase sentences should no be formated using the 'proper' formula."

and not like this:

"Uppercase Sentences Should No Be Formated Using The 'Proper' Formula."

View 3 Replies View Related

Macro For Splitting Worksheets But Excluding Certain Ones

Feb 7, 2013

I have a workbook with multiple worksheets, for instance Red, Blue, Green,Orange, etc. I want to split off everything except Red & Blue into separate workbooks.

I want each of the workbooks name to be saved as the value in a specific cell (stays the same for each book), say "A2". I also want to be able to choose the path of where to save these, all worksheets going to the same path user chooses on their comp, but each user may choose differently.

And lastly to keep these actions hidden from the screen when excel copying the worksheets.

View 4 Replies View Related

Macro For Splitting Data From Cell That Has More Than One Value In It?

Dec 5, 2013

Each row in my spreadsheet is a record. Unfortunately, for one cell, the data base that the info came from has in more than one piece of data in a cell in the data dump into Excel for that record. Each piece of data in the multi data cell is seperate by a ctrl enter hidden break.

I need a seperate record tied to the original record for each item in the multi data cell so that I end up with a flat file with unique records and data on one row.

Is there a way to do this other than manually? I'd like to have a macro since I run into this quite often depending on the client.

View 1 Replies View Related

Splitting Multiple Entries In Single Cell Into Multiple Columns

Jan 15, 2013

I am looking to split multiple different entries in a single cell into multiple columns and repeat this for all rows

Example (I have the below in a single Cell as column headers)
NCM Server Mgmt VLAN Site ID

Next Line down is the data (Each row in a single cell)
Enabled 10.10.10.0 50 TEST SITE 1
Enabled 10.10.20.0 50 TEST SITE 2
Disabled 10.10.30.0 50 TEST SITE 3

How I could achieve this as I have a number of projects where this would become useful

I know you can use delimiters but with spaces between the values I just can't fathom a way forward.

View 12 Replies View Related

Splitting Cell Data Into Columns / Data Cleaning

May 10, 2006

how to split data into columns.

I have a set of (imported) data consisting of 1000+ rows. The data set (bank statement) contain six informations (date1, transaction, vendor, date2, sum1, sum2) in one cell which has to be split into six columns.

Using text-to-columns will generate more than six columns since the set dosn't have any delimiters and both the transaction and vendor name-fields contain blank spaces.

Is there a way to either split the data into the correct number of columns or if text-to-columns is used - a way to manipulate the data into the correct columns, for instance with help of filtering ?

small data sample included

View 3 Replies View Related

Text File Import With Text To Columns Splitting Same Text Differently

Nov 21, 2007

I have a macro which imports data from a mainframe dump text file and performs 'Text to Columns' on the imported data so that formula in the spreadsheet can act on the data. The code works perfectly well when I use it, but if a different user logs on and performs exactly the same mainframe dump and import macro the Text to Columns action splits the raw data in a different way and the result is that the split renders the formulae useless.

I've experimented a little and for some reason it appears that the 'Field Info' parameters which are produced when the Text to Columns function is recorded in a macro differ between users even though the raw data is exactly the same.

FieldInfo:= _
Array(Array(0, 1), Array(18, 1), Array(35, 1), Array(56, 1), Array(70, 1), Array(88, 1), _
Array(102, 1))

View 6 Replies View Related

Macro To Unhide Columns - Call A Macro Then Revert Columns To Previous State

Jul 17, 2014

I have on sheet1 a number (72 at the moment) of Form CheckBoxes.

In simple terms: I would like a macro to look at each CheckBox and remember its state (Checked or Unchecked)

Then, go through and Check All checkboxes

Call MyMacro

Once MyMacro is complete (Filtering & Printing)

Revert the checkboxes to their original state.

The purpose of the checkboxes:

When Checked column on sheet2 is UnHidden
When UnChecked column on sheet2 is Hidden

Or, UnHide All columns on sheet2, run MyMacro, then "re-hide" the columns that were previously hidden.

View 11 Replies View Related

"Macro" For Splitting Data Into Separate Sheets

Aug 15, 2003

Scenario: Master List, which has all the U.S. states in it.

What I Need to Do With It: Divide out EACH state's information and put it on a seperate worksheet.

I've only recently started using macros (to format various documents for easier printing), but now that I'm starting to understand them conceptually, I'm thinking there Should Be some way for me to program a "macro"-type thing to run on the master list and come out with all my state lists, looking beautiful in seperate files.

View 9 Replies View Related

Macro To Compare Columns A & B And Dispaly Any Duplicates In Columns C & D

Feb 21, 2009

what I'm after is a macro to check the contents of Column 'A' against column 'B' and display any duplicates in Columns 'C' & 'D'.

N.B. The headings of Columns C & D are :-

C = Value Found in Column A

D = Value Found in Column B

Any duplicate entries logged in columns C & D should be listed in C2,C3,C4....C20 and D2,D3,D4......D20 etc (in effect creating two new lists)

View 5 Replies View Related

Macro Needed To Include Particular Columns Out Of So Many Columns In A Sheet

Apr 23, 2014

I have file with so many columns and i want to keep only columns i want.

Data
genredyellowgreenwhiteblue
1aaggttccbb
2aaggttccbb
3aaggttccbb
4aaggttccbb
5aaggttccbb

expected
genredwhite
1aacc
2aacc
3aacc
4aacc
5aacc

for example here i want to keep only gen, red and white columns only out of columns what i have in my data. I have so many columns in my original data but here i given just small example. How to proceed with macro or any other way because removing manually taking long time for me.

View 6 Replies View Related

Splitting Sum Value With Different Format

Apr 11, 2014

I need some formula how to sum data with different format (general & $ (currency), splitting summing,

check this below :

<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;

[Code] .....

View 4 Replies View Related

Splitting Averages...

Feb 2, 2009

If I have a varying number of figures(say between 4 and 10) which I want the average of the first half of the set and an average of the second half, is there a function which I can use to calculate this? ie if there is 8 numbers in total then I need the average of the first four and last four... but if there is ten figures total then I need the average of the first five and last five.

View 2 Replies View Related

String Splitting

Jan 23, 2007

If I have a string which contains 3 words, is there an easy way of separating out the consonants into one string and the vowels into another (spaces should be ignored)?

The only way I can think of doing it is to go through the string item by item and comparing the letters to a list of vowels and using that to do the separation.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved