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!

 

[DougStroud]

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.

If you have one, and it is not too much of an inconvenience I would like to use it. It would save a little time....

Thank you

 

[Joe4]

Note that if you goal is to automate the process, you can easily do the Text to Columns with a Macro.
Just turn on the Macro Recorder and record yourself doing the Text to Columns manually, and you will have recorded the code to perform this action via Macro.

 

[jtakw]

If you have one, and it is not too much of an inconvenience I would like to use it. It would save a little time....

Thank you

Hopefully this can handle your Long text strings, formula copied down (and across) as needed:

Book1
	A	B
1	0025_Billette_John_Wedding__S516914, 0029_Billette_John_Wedding__S516940, 0041_Billette_John_Wedding__S528265	Nathaniel_Sommer_Shalett_Bar_Mitzvah_S510499, Nathaniel_Sommer_Shalett_Bar_Mitzvah_S510500, Nathaniel_Sommer_Shalett_Bar_Mitzvah_S510505A, Nathaniel_Sommer_Shalett_Bar_Mitzvah_S510512
2	0025_Billette_John_Wedding__S516914	Nathaniel_Sommer_Shalett_Bar_Mitzvah_S510499
3	0029_Billette_John_Wedding__S516940	Nathaniel_Sommer_Shalett_Bar_Mitzvah_S510500
4	0041_Billette_John_Wedding__S528265	Nathaniel_Sommer_Shalett_Bar_Mitzvah_S510505A
5		Nathaniel_Sommer_Shalett_Bar_Mitzvah_S510512
Sheet124

Cell Formulas
Range	Formula
A2	=TRIM(MID(SUBSTITUTE(A$1,",",REPT(" ",LEN(A$1))),ROWS(A$2:A2)*LEN(A$1)-LEN(A$1)+1,LEN(A$1)))

 

[DougStroud]

Still getting the #VALUE ! error. Sorry

 

[DougStroud]

Thanks @Joe4- Great call.

 

[Joe4]

You are welcome.

 

[jtakw]

Still getting the [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=VALUE]#VALUE [/URL] ! error. Sorry

That shouldn't happen, did you change something within my formula?
If you change the Cell reference of the Target Cell, that's fine, but the part in Red below should be "Formula Cell (with absolute reference for Row $):Formula Cell (no absolute reference).

=TRIM(MID(SUBSTITUTE(A$1,",",REPT(" ",LEN(A$1))),ROWS(A$2:A2)*LEN(A$1)-LEN(A$1)+1,LEN(A$1)))

or show a sample string where you're getting #VALUE error and tell me what Cell it's in.

 

[DougStroud]

I will test it with the formula in your last post- but no, I did not change a thing. Copied/pasted in A2

 

[DougStroud]

Here is a link to a screenshot. I don't have permission to post attachments directly.
https://stroudimages.photoshelter.com/gallery/Excel/G0000x0s9sNZe.5M

 

[jtakw]

I think perhaps you posted the wrong link or uploaded the wrong image.

Please re-upload image and provide link, or upload just a sample file (that my formula isn't working) to Dropbox.