Emma has attended:
Excel Intermediate course
PowerPoint Introduction course
How do I split information into separate cells?
Hi,
Can you tell me how to split information from one cell to many?
e.g if I have a database of names and addresses and the address is all written in one cell, this needs to be split so that each line of the address appears in a different cell (for mail merge purposes).
In a large database is this possible as there are no commas to show breaks in the address lines and the breaks occur in different places in each cell.
Many thanks in advance,
Emma
RE: How do I split information into separate cells?
Hi Emma
If you have a delimiter, like a space or a comma, then you can use that with the DATA > TEXT TO COLUMNS command to split the data from the cell to multiple columns.
If the data has been entered using ALT+ENTER, so creating line breaks in each cell, that could prove to be more challenging.
I will do a bit more research and post an update.
Regards
Richard
RE: How do I split information into separate cells?
Right, got it.
The blow code will substitute commas in for all line breaks. Yuo can then use the DATA > TEXT TO COLUMNS easily.
Try this:
Open your VBA editor (ALT+F11)
Paste this code in:
Sub replace()
Cells.replace What:=Chr(10), Replacement:=Chr(44)
End Sub
Then put your cursor on that code, and click the PLAY button on the toolbar.
I suggest you make a copy of your data before trying this, as a safety measure.
Regards
Richard
RE: How do I split information into separate cells?
Thanks Richard.
I am not sure where the code that you refer to should be pasted. Also, where you say "paste this code" where do I paste it? And what exactly is the code? Is the following the actual code?
Sub replace()
Cells.replace What:=Chr(10), Replacement:=Chr(44)
End Sub
Sorry, I'm a bit lost on this!
Thanks,
Emma
RE: How do I split information into separate cells?
HI Emma
So you first need to open the VBA editor. Press ALT+F11.
That will open a blank module page. Then past the code, as you have above, into the blank page. You can then press the blue PLAY button / arrow, which will run the code.
This code will replace the characters that create the line break with commas.
Does that help?
Richard
