HELP! Need to convert comma delimited text into rows

[tbark]

Hello -

I have 65 rows of data; each cell contains anywhere from 5 to 8 comma delimited values that need to be housed in their own rows. Here is an example of my data:

Cell A2: 0x460297gpr,9806458rtgjd,0x5641313746fd,9x44564fs4f5s4f5ds3,5x4f35sd4f3sd4f3s
Cell A3: 3xsf23ds4dff,8xf4ds23df2s,1xf2sdfs3fds534f3s,2x5df45s34f5s3fds2,5x45fds34f5ds34fd5s3,7xdfs354fds5s
Continues through cell A65+ with similar values.

Here's where I need to get to: would love if I can do this in a macro or a formula. Doing it manually is not an option since this will go into another file where rows will be added to Column A with new and updated values. Here's my desired output:

1. the values in each cell get their own row
2. i then need to de-dupe the results and only show unique values
3. all of the results need to be in one column.

can someone PLEASE help me?

 

[dermie_72]

highlight the column, goto Data/Text to column, delimited, comma.
That will put the data into their own cell.

 

[tbark]

Hi dermie_72 -

Thank you for your advice. That actually was the first thing I did, but it goes not give me what I want. I puts the data in 5 columns and about 200 rows. I need it in 1 column, so the result would be 1 column and 1000 rows. Do you know if/how that is possible?

Thanks,

 

[dermie_72]

this code should do what you want.

Sub tbark()
'for http://www.mrexcel.com/forum/excel-...d-convert-comma-delimited-text-into-rows.html
'Assumes that the data has already been delimited to multiple rows/columns
'
Dim LR1, LR2, LC As Long
LR1 = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
LR2 = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row

For I = 1 To LR1
    LC = Sheets("Sheet1").Cells(I, Columns.Count).End(xlToLeft).Column
    Cells(I, 1).Resize(, LC).Copy
    Sheets("Sheet2").Range("A" & LR2).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Application.CutCopyMode = False
    LR2 = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
Next I
End Sub

 

[tbark]

Hi dermie_72 -

You are such a life saver - thank you for helping me.

I'm having difficulty having the macro work. Here's what I'm doing and what my file looks like - perhaps something in the setup of my file is not syncing with how the macro is written.

1. All of my raw, pre-delimited data is in cell A1 of Sheet1 and goes until cell A67.
2. When I delimit A1 to A67, the results span into cells B1 to P67. Note that some of the cells in between B1 and P67 are blank since not all of the original data in row A has the same amount of delimiting needed.
3. I have another sheet called Sheet2 which is currently blank.

Any idea why your wonderful macro is not functioning? It runs and I don't get an error, but no results populate into Sheet2. I have tried putting the delimited results in cell A1 instead of B1, but I still get no results populated into Sheet2. Help, please?

Thank you again so very much!

 

[tbark]

Hi again dermie_72 -

I solved the problem - it works perfectly now! What an amazing macro - thank you for totally saving me so much time and frustration!

 

[dermie_72]

Good work tbark. Glad that you got it all working. Thanks for the feedback, it is most appreciated.

 

[tbark]

Hello - original poster here with another question that I'm hoping someone can help with. The macro is working out GREAT, but there's (I fear) another element that I need to add to it.

My original data (before it is delimited and the macro runs), has 3 additional columns next to it with unique values in it. Turns out, I will need to now create a report based on the results of the macro (where the delimited values from column A reside in their own cell), and have the corresponding values from columns B, C and D in the file now too! Is that even possible in this macro?

Here's a recap/example of the data in my file:
Column A:
Cell A2: 0x460297gpr,9806458rtgjd,0x5641313746fd,9x44564fs4f5s4f5ds3,5x4f35sd4f3sd4f3s
Cell A3: 3xsf23ds4dff,8xf4ds23df2s,1xf2sdfs3fds534f3s,2x5df45s34f5s3fds2,5x45fds34f5ds34fd5s3,7xdfs354fds5s
Continues through cell A3000+ with similar values. Note that when the file is delimited, the results can stop in either column D or column H since there can be anywhere from 3 to 8 values in a cell prior to it being delimited.

Column B: titles ('Top 5 Books', 'Top 8 Cities', 'Top 7 Recipes')

Column C: category (literature, travel, cooking, etc....)

Column D: business unit (personal use or business)

The resulting file would have 4 columns with x+ rows with data being populated in each of the four columns.

dermie_72 was awesome to send the following macro and it works like a dream. Can this macro be added on to so that I get my results in a format that I can then create a pivot table off of or do I need to set up something more complicated? PLEASE HELP ME!!!!

Current macro used:
Sub editorial()
'for HELP! Need to convert comma delimited text into rows
'Assumes that the data has already been delimited to multiple rows/columns
'
Dim LR1, LR2, LC As Long
LR1 = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
LR2 = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row


For I = 1 To LR1
LC = Sheets("Sheet1").Cells(I, Columns.Count).End(xlToLeft).Column
Cells(I, 1).Resize(, LC).Copy
Sheets("Sheet2").Range("A" & LR2).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
LR2 = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
Next I
End Sub

 

[Rick Rothstein]

My original data (before it is delimited and the macro runs), has 3 additional columns next to it with unique values in it. Turns out, I will need to now create a report based on the results of the macro (where the delimited values from column A reside in their own cell), and have the corresponding values from columns B, C and D in the file now too! Is that even possible in this macro?

Here's a recap/example of the data in my file:
Column A:
Cell A2: 0x460297gpr,9806458rtgjd,0x5641313746fd,9x44564fs4f5s4f5ds3,5x4f35sd4f3sd4f3s
Cell A3: 3xsf23ds4dff,8xf4ds23df2s,1xf2sdfs3fds534f3s,2x5df45s34f5s3fds2,5x45fds34f5ds34fd5s3,7xdfs354fds5s
Continues through cell A3000+ with similar values. Note that when the file is delimited, the results can stop in either column D or column H since there can be anywhere from 3 to 8 values in a cell prior to it being delimited.

Column B: titles ('Top 5 Books', 'Top 8 Cities', 'Top 7 Recipes')

Column C: category (literature, travel, cooking, etc....)

Column D: business unit (personal use or business)

The resulting file would have 4 columns with x+ rows with data being populated in each of the four columns.

Are Column A, B, C and D tied to each other in some way? What I mean is if Column A's cell has 4 delimited values in it, must the other columns on the same row also have 4 delimited values each as well? If not, how should Column B, C and D's delimited data be distributed (or is it even delimited)?

By the way, before you changed your request, I was going to post this non-looping macro for you to consider...

Sub RedistributeCommaDelimitedData()
  Dim X As Long, Arr() As String
  Arr = Split(Join(Application.Transpose(Sheets("Sheet1").Range("A1").Resize( _
        Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row).Value), ","), ",")
  Sheets("Sheet2").Range("A1").Resize(UBound(Arr) + 1) = Application.Transpose(Arr)
End Sub

 

[tbark]

Hi Rick,

THANK YOU for your reply! Appreciate your help very much

The values in columns B, C and D will ALWAYS contain only 1 value, so no delimiting of those columns will ever be necessary. I really just need to get Col A delimited and in one single column with 1 value in each cell, which I already have. I then need to map the results in the 'new' Column A back to the values that were mapped to them in the un-delimited version. I there was a way to vlookup one of the values in a pre-delimited string and then bring back the corresponding values in Columns B, C and D, then that would work, but I don't see any way of doing that with accuracy since the values in Column A will repeat and often mapped to different values in columns B, C and D. Does that make sense? I really hope so as I'm desperate for a solution

Thanks!
tbark