how to split data where line breaks are the delimiter

J

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 [email protected]
tel 01483 522563


any suggestions please! thanks
 
G

Glenn

Jackie said:
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 [email protected]
tel 01483 522563


any suggestions please! thanks
Click to expand...

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.
 
P

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
 
G

Gord Dibben

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


Gord Dibben MS Excel MVP
 
Top