Perhaps this formula, which assumes your sample entry is in cell A1:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,CH AR(10),",",1),CHAR(10),",",1),CHAR(10),",",1),CHAR (10),",",1)

I believe you'll find that [Alt]+[Enter] is ASCII code 10, that's why the
CHAR(10) in the formula.

I ended up with: 1 Luke Road,Luke Town,Luke City,Lukeshire,LU1 1KE
in cell B1 (where I had placed the formula). From that point I can use
Edit-- Copy on all cells with similar formulas in them followed by Edit --
Paste Special with the "Values" option chosen to convert the formulas to
actual text that can then be moved to columns using Text To Column.


"LukeLibrarian" wrote:

Hello,

I have a column of over 600 addresses, and they are entered as follows:

1 Luke Road
Luke Town
Luke City
Lukeshire
LU1 1KE

When unwrapped, it obviously just looks like one long sentence with squares
where the (ALT+Enter)'s were.

My issue is that I would like to split each line of the address out into
adjacent columns. Any help would be gratefully received, and save me a few
hours of manually typing in commas so that I can TTC it!

--
Thanks
Luke