Split Single Text Cell Into Multiple Rows Using Comma Delimiter?

Jul 3, 2014

I want to split the contents of a single cell(ALT Enter as delimiter) into multiple cells and retain the values in column B.

Ex:

A1: apple
banana
car
house

A2: yellow

B2: building
x
y

B3: O

Output:

sheet 2:

A1: apple B1: yellow
A2: banana B2: yellow
A3: car B3: yellow
A4: house B4: yellow
A5: building B5: O
A6: x B6: O
A7 B7: O

View 9 Replies


ADVERTISEMENT

Split Single Text Cell Into Multiple Rows, Using A Comma Delimiter

Dec 1, 2008

I have a string of text in one cell on Sheet 1 (ie., A1, Sheet 1), here is a excerpt:

A-dec International Inc., A. Bellotti, A. DEPPELER S.A., etc ...

What I need to do is split the cell into separate rows, using the comma as a delimiter. I will be reading the cell from another sheet and need a formula that will provide me with

A1: A-dec International Inc.
A2: A. Bellotti
A3: A. DEPPELER S.A.

View 9 Replies View Related

Reformat Text In Single Column To Rows Using Delimiter

Jun 10, 2008

I am trying to convert text to rows but in the same column Take the following example:

Column A:
1-2-3
ab-cde-fg
54-ty-12345
the
waht i am looking is convert it to the following format: (the - is the delimiter)
Column A

1
2
3
ab
cde
fg
54
ty
12345

i have found the following VBA code on those forums but i don't know how to edit it to suit my needs

Sub Txt_To_Rows()
Dim arrText() As String
Dim varItm As Variant
Dim rngText As Range
Dim rngCl As Range
Dim i As Integer
Dim j As Integer
Dim x As Integer
Set rngText = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
j = 2...........................

View 2 Replies View Related

Split Some Text In Range If It Does Not Have Delimiter?

Apr 24, 2012

How do i split some text in range if it does not have a delimiter? I would like to have a comma after each letter/number or have the option to split to individual cells

I know how to use the vba split function with a delimiter but cant figure this out.

I.e susanna
S,u,s,a,n,n,a.

View 4 Replies View Related

Split Single Column Into Multiple Columns And Rows

Aug 20, 2008

I have 300 rows worth of data that looks similar to this, all organized in one column:

John Q. Smith
Programmer
2111 NW 13th St
Anywhereville, USA, 55555
(555) 555-5555
Joe P. Snider
Organizer
5645 NW 45th St
Anywhereville, USA, 55555
(555) 555-5555
Patty Williams
Accountant
6454 NW 34th St
Anywhereville, USA, 55555
(555) 555-5555

As you can see it is consistent with the name, position, address line 1, address line 2, Phone number, for every single entry. All my names are already alphabetized so I don't have to worry about it. What I want to do is have the information for each entry translated into 5 separate columns so it looks like this.

John Q. Smith Programmer 2111 NW 13th St Anywhereville, USA, 55555 (555) 555-5555
Joe P. Small Organizer 5645 NW 45th St Anywhereville, USA, 55555 (555) 555-5555
Patty Williams Accountant 6454 NW 34th St Anywhereville, USA, 55555 (555) 555-5555

Supposing I started the sheet in the top left corner at A1, I was just going to have cell B1=A1 then Cell B2=A6 then just autofill down column B but it doesn't work. I have seen some people do something similar to what I want with VBA but I am convinced there has to be something simple in with an excel formula seeing as my information is already so organized and consistent.

View 4 Replies View Related

Split Text And Number Field With No Delimiter?

Feb 14, 2014

I am looking for a solution to split text from numbers.I have found a couple examples on the web but I cannot get the examples to work with the correct syntax to function.

Sample cells. The string could be any integer or floating point number with text. (The text is always after the number.)

1234txt
12.222txv
102.1hknn

Result needed
col1
1234
12.222
102.1

col2
txt
tvx
hknn

View 4 Replies View Related

Multiple Comma Separated Lookup Results In Single Cell

Mar 13, 2008

I have a cell witch contains CSV I need too look up all the values off a master list an out put the vlookup results in 1 cell

Example:

( SHEET 1/Cell A1)

10,11,12

(SHEET 2/ look up list)
.A .B
110101
211102
312103
413104
514105

(SHEET 1)
I need the output to show the following in Cell B1

101,102,103

View 9 Replies View Related

Adding Comma As Delimiter Before Number In Cell For Entire Worksheet?

Oct 7, 2011

I do receive invoices from my vendors. They send me an invoice in PDF formate. I am using able2extract software to conver the PDF to excel. It is working great but most of the times my vendors enters the items/Case in the description field. Like the example below:

"Bajri Rotla 11 oz.(24)"

I would like to place a "," in between the last character and begining of the first number. In the above case it will be after "a" (in Rotla) and before "1" in (11). If I can do this then I can apply texttocolumn from excel and use "," as delimiter and separate item name from size and case/Pack. Then can use another "Texttocolumn" and separate size and case/pack. If there is a VBA code that can be written for this it would make my life a whole lot easier.

Following are 3 images that I have copied from actual data.

1. Original PDF invoice data that I receive from my Vendor

2. Image of the excel file before formating. This is the format from "Able2Extract" (3rd party software that I use to convert PDF to excel. Works great!!

3. Converted Excel data sheet. This is the way I want to format the data.

View 7 Replies View Related

Split Cell By Delimiter, Move To New Row

Sep 3, 2008

I'm prepping this file to be pushed through mail merge. The sheet has 25,213 rows.

Column I contains a long string of character (for the purposes of my project the field is called Subjects) and $ used as delimiters (approx 70 or as few as 1). This column needs to be split apart by $ and the separate Subjects that result from splitting apart by $ needs to be placed on a separate line. Once each Subject is on a separate line, all of the Subjects need to be lined up in Column I, one under another (the obvious result at this point is GREATLY increasing the number of rows in the sheet). Next, all of the data that is contained in each Subject's originating row will be copied to each of newly created rows (without copying over the Subject contained in column I).

After writing this all out, I realize (once again).

View 14 Replies View Related

Lookup Multiple Values In A Single Cell (separated By Commas) And Then Return The Values To A Single Cell (also Comma Separated)

Jan 7, 2009

If I have, in one cell (call it D1):

EH,DR,HU

and in a lookup table on another sheet:
A B
1 ED T
2 EH F
3 DR G
4 HU H
5 SE E
6 YU E

I need to be able to lookup the values in D1 on the table and return the values in column B to a single cell (say E1), also comma separated...

eg...

F,G,H

View 9 Replies View Related

Split Multiple Dates Stored In Single Cell

Jun 14, 2008

how to read mutiple values from one cell. For example i want to have one cell for site visits, and there may be more than one. So i want to let the user enter more than one date seperated by a ",". Is there anyway of going about this?

View 5 Replies View Related

VBA - Split Comma Separated Entries To New Rows?

Feb 4, 2013

I have a relatively large data set that is separated into columns A-G. In column G, I have a multitude of values that are separated by commas. What I would like to do is to separate these entries into rows while keeping those values in A-F constant.

Here is an example of what I would like to do through VBA:

ex1.jpg

to

ex2.jpg

Also, kudos. This website has now gotten me interested in VBA.

View 6 Replies View Related

Split Multiple Comma Separated Columns Using VBA?

Jan 18, 2012

I am supposed to create a macro which will split the comma separated values into rows.

My main constraint is, I have 3 columns of comma separated values.

Let me explain with an example,
I have 5 columns namely:
A B C D E
123 1,2,3 1,2,3 123 1,2,3,4

Once I run a macro it should show like below:

A B C D E
123 1 1 123 1
123 1 1 123 2
123 1 1 123 3
123 1 1 123 4

[Code] .......

View 6 Replies View Related

Split Address Which Is In Single Column Which Contains First / Last Name And Address Without Any Comma?

Apr 24, 2013

I have a list for addresses in excel in single column as shown below - aanandhi narayanan 3430 chemin de riviere sanjose,CA95148

DOMINIC ABANO 3838 GLENGROVE WAY SAN JOSE, CA 95121

abdi abdi 5390 monterey rd #6 sanjose,CA95111

Sheribel Abinsay 3212 Gateland CT San Jose, Ca 95148

I need the result to be in a way like -

3430 Chemin de riviere
San Jose
CA
95148

3838 Glengrove way
San Jose
CA
95121

5390 monterey rd#6
San Jose
CA
95111

3212 Gateland CT
San Jose
CA
95148

I have around 12000 records with the same format.

View 9 Replies View Related

Split Comma Separated Text And Insert Into Next Row Retaining All Other Columns

Oct 26, 2013

Original table:
Column1
Column2
Column3
Column4

AAA
DDD
A1
X123, Y123, Z123

[Code] .......

View 1 Replies View Related

Need Macro To Create Formulas And Split (Comma Delimited) Cells Into Rows

Mar 30, 2013

Example: Column A has a mixture of letters and numbers. ie AU1234 or AU5678 Always the letters will be first, but not sure if 2 or 3 letters. Need to insert space between letters and numbers.

I have so far. " =(left(a2,2)) & " " & (mid(a2,3,(len(a2)-2))) " this works if all are only 2 letters...

Now. What I need to do is open a .csv (will do manually) then hit something like ctrl-alt-k to run macro.

Step 1: Insert a column next to A, check rows down and for however many rows, make above formula (include 2 or 3 letters) to insert space between letters and numbers, select the new column, copy, select column a and overwrite with the values from the new column. ie turn 'A2' from "AU1234" to "AU 1234" and 'A3' from "AU4567" to "AU 4567" .

Step 2: Column D has comma delimited fields. Column F also has comma delimited fields. both D and F will always have the same number of fields. D will be something like 1234,2345,3456 ------ in this case 3 fields but could be over 100 fields
F will be something like M0002456 (04P), M0002457 (05P), M1230477 (02A).

Need to split both D and G from row A2 simultaneously from comma fields to rows. copying all other data from row. and insert before the next set of data in what was previously A3 and (in this case *should* be moved down to A5 because of the 2 inserted lines from the 2 extra fields).

E.g.: Column A Row 2 "AU 1234" Column B Row 2 "data1" Column C Row 2 "data2" Column D Row2 "1234" Column E Row 2 "data3" Column F Row 2 "M0002456 (04P)"
Column A Row 3 "AU 1234" Column B Row 3 "data1" Column C Row 3 "data2" Column D Row 3 "2345" Column E Row 3 "data3" Column F Row 3 "M0002457 (05P)"
Column A Row 4 "AU 1234" Column B Row 4 "data1" Column C Row 4 "data2" Column D Row 4 "3456" Column E Row 4 "data3" Column F Row 4 "M1230477 (02A)"

Then carry on to next row which may have only one field and can be ignored/skipped to the next which may have 100 fields which will need to be split to rows and inserted...etc....

Step 3
Remove all the "space Bracket-data-Bracket" ie " (04P) from column F

View 7 Replies View Related

Transpose Column Range Into Single Cell With Delimiter

May 14, 2008

I want to add a Punctation mark (comma), like this: ,
and also want to add punctation mark (colon), like this: :

In this moment I have below macro:

Public Sub CombineCells
Dim Combined As String
Combined = ""

For Each Cell In Selection
Combined = Combined & Cell.Value & ":"
Next Cell

Selection.Cells(1, 4).Value = Combined
End Sub

the effect shoud be like this:

before:
--A
1-C
2-D
3-E
4-F
Etc.

after transposed:
--D
1-C:D,E:F Etc.

View 3 Replies View Related

Split Cell Into Multiple Rows

Jan 12, 2007

I have a datasheet that has more than 80,000 rows, Current format is:

Coulmn A - Column B
prod1 - <option selected>abc</option><option>def</option><option>ghi</option>

I want it converted into:

Coulmn A - Column B
prod1 - selected
prod1 - abc
prod1 - def
prod1 - ghi

View 3 Replies View Related

Split Multiple Values In A Cell To Multiple Rows?

Mar 4, 2013

if there is a way to split multiple values in a cell to multiple rows. example check the attached sheet, macro/function with in excel etc.

View 2 Replies View Related

Split 1st & Last Names & Split Addresses After 1st Comma

Mar 6, 2008

I'm using Excel 2000 and I have a spreadsheet with 4 columns (A-D) and many (500+) rows.

Part 1:
#########################################
Colums A & B both contain identical data - a first name and a last name in the format "John Doe".

I want the second word ("Doe") removed from all cells in Column A so that only the first name remains, and I want the first word ("John") to be removed from every cell in Column B so that only the last name remains.

So, where A1 & B1 both started with the data "John Doe" now A1 contains only "John" and B1 contains only "Doe".
#########################################

Part 2:
####################################################
Column C contains addresses in the format:
"#5 - 123 Fake Street, Some City, CA 90210"

There is ALWAYS a comma and a space after the street address, then the name of the city or town followed by more data which may include one or more commas.

I would like everything BEFORE the first comma to remain in column C, and everything AFTER the first comma & space to be moved into Column D of the same row. The first comma and space are not needed again.

So, where C1 started with "#5 - 123 Fake Street, Some City, CA 90210", it now only contains "#5 - 123 Fake Street" and D1 now contains "Some City, CA 90210".
####################################################

View 6 Replies View Related

Split A Cell Of Data Into Multiple Rows?

Mar 22, 2013

I have some data from web-site, when I copy paste these data to Excel is only in 1 cell and i would like to split it into multiple rows by comma.

View 4 Replies View Related

Split Cell Data In Multiple Rows

Jul 18, 2006

I was just not able to figure out how to write a macro for splitting the cell data into multiple rows with the other column values being duplicated. Just elaborating my problem, its like i have a column in which i have multiple values separted by a comma, So i need to divide all these values on basis of a comma and then copy this value to a new row below, along with the previous column values same for this new row. in my file cost center column is to be worked upon. Can this be done by a macro ?

View 5 Replies View Related

Split String At First Delimiter Only

Nov 11, 2008

I have a column with cells that look like: XYZ - JobABC - Area 1-A. I'd like to split the column into to and have it look like: XYZ <next column> Job ABC - Area 1-A
Normally I'd use the text to columns function and "-" as delimiter, however, there are other instances of "-" which I do not want separated. Note that the XYZ can vary in length and therefore fixed width will not work either. I have attached a small sample with the different variations that I could encounter.

View 7 Replies View Related

Split Cells Into 2. Space As Delimiter

Oct 18, 2006

I recently got this task by my work employer to transfer information from several non-organized Excel-documents into one, complete and organized one.

The thing I have to organize right now is a column with thousands of zipcodes and zones in one single cell, only separated by spaces, like this:

Zipcode Zone
115 54 Gothenburg
443 67 Skane

What I want to do is instead of having to go through 24h of braindead control/paste is to use some easy and fast function or script that could pull out the last piece of string of information in every cell in that column into its own separate cell.

Although I am using Open Office Calc/Gnumeric on Ubuntu Dapper, if a script is necessary and only work with Excel on Windows/Mac, that is no big problem for me to solve. So please, any suggestion is welcome.

I will attatch two examples for you to look at, to get the whole picture.

View 8 Replies View Related

Split Column On 2nd Occurrence Of Delimiter

Feb 28, 2007

I have one column in a worksheet that I want to split into 2 columns. If there is one "(" in the cell, I want to split at that point, but if there are two "(" 's, I want to split at the second occurrence. I can use the Like operator to identify the cells, but is there a way to specify which delimiter in a sequence of identical delimiters to split at? Here are templates (not actual data) of the two type of cells:

Name1 (ABC) (Name2, Name3)
Name4 (Name5, Name6)

For the first, I want to split on the second "(", before Name2, and for the second, I want to split on the first "(", before Name5.

View 4 Replies View Related

Keywords As Delimiter To Split Cells

Jul 1, 2008

I've got a column of data containing addresses. What I want to do is split the column after certain keywords such as "street", "road", "close" etc and move any words that come after these in the cell into the next cell to the right.

For example, in one cell I might have "54 Burberry Road Finsbury London"... what I want to do is have part of the text remain in the original cell i.e. "54 Burberry Road" and the rest, "Finsbury London", moved to the cell on the right.

Not all cells in the column will match this format so if it doesn't find a keyword, nothing will occur and it will move to the next cell down etc. until it reaches the end of the column.

There are no delimiters (apart from the spaces) between the words.

I haven't figured out all of the keywords that I will need to search for yet but I should be able to extrapolate from something fairly generic.

View 5 Replies View Related

Concatenate Multiple Rows In A Column Using Delimiter Based On Changing Value

Aug 16, 2013

I am attempting to concatenate the dept numbers for each user in a list. For example, the first user listed below is associated with 6 depts. I would like the resulting macro to concatenate the dept's into one cell next to the Name.

Below is an example with the solution I am attempting to produce.

Sample Table

Name
Dept

Abbruzzese,James L
188100

[Code] ......

Output

Name
Dept

Abbruzzese,James L
188100, 231100, 600377, 600656, 600663, 600708

Abdi,Salahadin
600607, 600670, 600878, 600879, 710432, 710432, 710435

View 2 Replies View Related

Split Excel Cell With Carriage Returns Into Multiple Rows?

Jul 6, 2014

I have a situation where I have source data formatted like this:

Document ID
Document Name
Author

[Code]....

What I would like to do is to get each author on a separate row. I am able to do that using Text to Columns using the carriage return and then doing a transpose. I've also seen some threads where VBA is used to accomplish similar. However where I am stuck at is getting everything else to drop down accordingly. I.e. with the data set above I'd like to get to:

Document ID
Document Name
Author

[Code]....

View 3 Replies View Related

Count Comma Separated Values In A Single Cell

Jan 25, 2007

I need to count the number of values entered into a single cell seperated by commas for record keepin purposes. I would prefer not to use a macro since I am not an expert, but will if i must. Here is an example of what i am talking about:

User A will enter "109,108,107,106" into a cell in Column A

At the bottom of column A the Total will read 4.

Now User B enters "110,111,112,113" into the next cell in Column A

At the bottom of column A the Total will read 8.

View 4 Replies View Related

Join Data Into Single Cell As Comma Seperated

Aug 9, 2007

I am attemping to transpose a column of data into a single cell. I have a list of contact information, and one of the columns is for e-mail addresses. There are filters to the right of the data with different categories so the contact information can be filtered according to the different criteria. Ultimately, the people using the spreadsheet want to use those filters to create a list of e-mail addresses to send their distributions to.

The people who are using this don't have Outlook so we can't use an output to add them to the Outlook address book. We would like to make one cell at the bottom of all the data which would include all the e-mail addresses from that column seperated with a semi colon so they can just copy the contents of that cell and paste that into the To: field on their e-mail program. I've tried several different things but I've not been able to find a solution.

View 2 Replies View Related







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