Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

excel computer training - how do i split

Forum home » Delegate support and help forum » Microsoft Excel Training and help » excel computer training - How do I split information into separate cells?

excel computer training - How do I split information into separate cells?

ResolvedVersion Standard

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

Excel tip:

Create and delete borders

To put a border around the outside of a selected range, press Ctrl+Shift+&. Use Ctrl+Shift+_ (underscore) to remove any borders from a range.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.11 secs.