Split Text List Into Individual Cells

[DougStroud]

I have a text list copied from the clipboard that pasted everything into one cell. I would like to split the individual records into their own cells, in the column. The records are separated by a comma. Here is a short example of the text list:

0025_Billette_John_Wedding__S516914, 0029_Billette_John_Wedding__S516940, 0041_Billette_John_Wedding__S528265



Thanks for the help!

 

[jtakw]

Hi,

Formula copied across as far as needed:

Book1
	A	B	C	D
1	0025_Billette_John_Wedding__S516914, 0029_Billette_John_Wedding__S516940, 0041_Billette_John_Wedding__S528265	0025_Billette_John_Wedding__S516914	0029_Billette_John_Wedding__S516940	0041_Billette_John_Wedding__S528265
Sheet74

Cell Formulas
Range	Formula
B1	=TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",500)),COLUMNS($B1:B1)*500-499,250))

You can also use Text to Columns using the comma as delimiter.

 

[DougStroud]

My apologies for the long delay in replying, had a family emergency and just getting back home now. I tried the formula offered and it worked up until column H and then I-O are empty and then P result is "121" and then Q starts up properly again. I then tried it on another list with this text- Nathaniel_Sommer_Shalett_Bar_Mitzvah_S510499, Nathaniel_Sommer_Shalett_Bar_Mitzvah_S510500, Nathaniel_Sommer_Shalett_Bar_Mitzvah_S510505A, Nathaniel_Sommer_Shalett_Bar_Mitzvah_S510512, on a separate sheet, Sheet 2 in the same workbook and I get an error- #Value !. Could you look at this and see what might be different or what I may be doing wrong?

 

[jtakw]

No problem.

I believe it's because your Text Strings are just WAY longer than I anticipated, so my formula as is, just can't handle it ( if we're starting at Column A, and you're talking about Column Q and beyond ). I can tweak my formula to accommodate.

Before posting an updated formula, please confirm whether you want it Extracted in the same Column or Row? ( To the Right, or Downwards )

 

[DougStroud]

Thank you!
Ideally, I would like it presented in the column beginning one cell below, A2. Thank you again...

 

[Joe4]

Is there a reason why Text to Columns cannot be used here, delimiting the data on a comma?

 

[DougStroud]

@Joe4, no, no reason other than I had forgotten how to use it. @jtakw mentioned it in his initial reply and I couldn't find it so I thought it had to something to do with the way it was being copied over. I did some searching now and re-discovered it and that approach works just fine. Thank you and thank jtakw!

 

[Joe4]

Yes, Text-to-Columns comes in very handy at times! It is pretty easy, and I use it a lot.
Glad it works for you!

 

[DougStroud]

Thanks

 

[jtakw]

Thank you!
Ideally, I would like it presented in the column beginning one cell below, A2. Thank you again...

You're welcome.

I suppose you no longer need an updated formula to separate the text string down Column.
If you still want it, post back.