clean() break lines - creating space after

[lezawang]

Hi
I used alt+enter to enter the following in one cell.

John Smith
123 Main Street
MyCity, 12345

Then I used Clean() to clean break lines. But I ended up with a text look like this

John Smith123 Main StreetMyCity, 12345

How can I create a space between "John Smith" and "123 Main Street" and between "123 Main Street" and "MyCity, 12345" ?

Thank you so much.

 

[Norie]

Don't use CLEAN?

If it's only the ALT+ENTER character you want to get rid of try SUBSTITUTE.

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

 

[Rick Rothstein]

Then I used Clean() to clean break lines. But I ended up with a text look like this

John Smith123 Main StreetMyCity, 12345

How can I create a space between "John Smith" and "123 Main Street" and between "123 Main Street" and "MyCity, 12345" ?

Don't use CLEAN, use SUBSTITUTE instead...

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