Extracting data from multi-line entry

[Kentman]

I have a workbook supplied by someone else which contains cell entries with multi-lines - can I extract these by line?

 

[Gerald Higgins]

Can you explain in more detail what you mean please ?

 

[Kentman]

Gerald the user has put name, address, postcode etc in a single cell but has used Alt>Enter to wrap line as in: Kentman Some address Some postcode

 

[Gerald Higgins]

I see.

I can't think of a neat way to do this.
Normally I would suggest something like Data, Text to Columns, possibly after doing Edit, Find and Replace, but I can't either of those work with the character that alt+enter is giving you, which is probably =CHAR(10).

What you COULD do, is a bit cumbersome, but something like this

=mid(a1,find(char(10),a1,1),100)

and repeat this (adapted as required) for each element within the address.

There is probably a neater way of doing this . . .

 

[Kentman]

Gerald - thanks that gets me somewhere near so I'll work on it.

 

[Gerald Higgins]

Sorry Kentman, there is a MUCH easier solution, I should have thought of it before, try the CLEAN() function.

 

[Kentman]

Update:

The best way I've found of doing the extract is to use the SUBSTITUTE function to turn line feeds into characters, I use the pipe "|" as it is very seldom used in normal text: =SUBSTITUTE(A2,CHAR(10),"|") > copy and past your column with the delimited values to a text file > save the text file > open it in excel using the delimited option and select the pipe as the delimiter.

You can now copy the values back into your original sheet.

 

[Sektor]

Watch this post.