how to split data where line breaks are the delimiter

[Jackie Pearce]

i'm trying to split data which is seperated by line breaks into seperate
columns. line breaks are not in the standard delimiter list and i cant find a
way or doing this.

ie
John Smith
23 Tree Terrace
London
SW3
email (e-mail address removed)
tel 01483 522563


any suggestions please! thanks

 

[Glenn]

i'm trying to split data which is seperated by line breaks into seperate
columns. line breaks are not in the standard delimiter list and i cant find a
way or doing this.

ie
John Smith
23 Tree Terrace
London
SW3
email (e-mail address removed)
tel 01483 522563


any suggestions please! thanks

Assuming your data starts at A1, put the following formula in B1:

=SUBSTITUTE(A1," ","~")

In between the empty quotes, instead of a space hit Alt-Enter.

Copy the formula down to match your list of data. Then select all of the
formulas and Copy, then Paste / Special / Values.

Now do a Text To Columns using "~" as the delimiter.

 

[PCLIVE]

You might try replacing your line breaks (assuming that they are in the form
of alt+enter) with a character such as "~" or "`".
=SUBSTITUTE(A1,CHAR(10),"~")

Once you've done that, copy that result and PasteSpecial, values only. Then
you can use Text to Columns using "~" as the delimeter.

HTH,
Paul

 

[Gord Dibben]

Data>Text to Columns>Delimited by>Other........CTRL + j and OK


Gord Dibben MS Excel MVP

 

[Jackie Pearce]

thanks all, very helpful and a great time saver!

J