Rewriting a sentence in a cell

Brass Contributor

Hi everyone

I have a data and lots of senteces are written in cells like below:

HERMES AGENCIES S.A.
18th, 28th Octovriou Str.
P.O.Box 1132
GR-57009 KALOHORI
GREECE
TEL:2310778825
FAX:2310778875

And I need to rewrite the sentece like below for each cell:

HERMES AGENCIES S.A. 18th, 28th Octovriou Str. P.O.Box 1132 GR-57009 KALOHORI GREECE TEL:2310778825 FAX:2310778875

 

Please help me if you know any possibility to do that stuff.

Also added an example file to make you understand better the data I have.

 

 

2 Replies

@Anıl Adaş  not sure why you need to do this but you can create another column and use this formula:

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

 

here is an example of how it worked:

mtarler_0-1602797969887.png

notice how A2 has the line returns but in B2 it is just normal word wrap.

 

@mtarler
Thanks a lot for your reply

I used CLEAN function and solved my problem but also tried yours[=SUBSTITUTE(A2,CHAR(10)," ")] and it was very helpful for one of my projects