Jackie Pearce wrote:
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
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.