Multiline Cell ---> SingleLine Cell with space

[watsindename]

I have a cell with entries as follows for example

abcd
efgh
ijkl

i would like to split it over multiple columns.

Let me be a little more specific

my data is arranged as follows in the cell

abcd<space><enter>
efgh<space><enter>
ijlk<space>

I want it as follows in ONE cell as follows

abcd<space>efgh<space>ijkl

OR

Column 1 Column 2 Column 3
abcd efgh ijkl

Things I have already tried.

1. "Data -->Text to columns" : doesn't work for some reason, only my first string "abcd" is being read. Please do remember I have more than a thousand entries so manual change on each cell would be difficult.

2. =clean() : removes the spaces making data splitting impossible.

3. =trim() : same as above

I need help on this asap guys
Thanks a ton in advance

[sweep]

Hi,
try this:

=SUBSTITUTE(A2,CHAR(10)," ")

[JBeaucaire]

...and then these three formulas can do the "alternate output" for you in three separate cells:

D2:
=LEFT(CLEAN(A2),FIND(" ",CLEAN(A2))-1)

E2:
=MID(CLEAN(A2),FIND(" ",CLEAN(A2))+1,LEN(CLEAN(A2))-LEN(D2)-LEN(F2)-2)

F2:
=MID(CLEAN(A2),FIND("|",SUBSTITUTE(CLEAN(A2)," ","|",2))+1,99)

Note: The formula in the middle won't work until the both the outer formulas are in place.

[royUK]

You can use the VBA Split Function to separate the contents to separate olumns

Please Login or Register  to view this content.

[royUK]

Thanks for the rep points, but can you say whether this is Solved & if so mark it Solved