Rewriting a sentence in a cell

%3CLINGO-SUB%20id%3D%22lingo-sub-1786009%22%20slang%3D%22en-US%22%3ERewriting%20a%20sentence%20in%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1786009%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EHi%20everyone%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EI%20have%20a%20data%20and%20lots%20of%20senteces%20are%20written%20in%20cells%20like%20below%3A%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EHERMES%20AGENCIES%20S.A.%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3E18th%2C%2028th%20Octovriou%20Str.%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EP.O.Box%201132%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EGR-57009%20KALOHORI%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EGREECE%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3ETEL%3A2310778825%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EFAX%3A2310778875%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EAnd%20I%20need%20to%20rewrite%20the%20sentece%20like%20below%20for%20each%20cell%3A%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EHERMES%20AGENCIES%20S.A.%2018th%2C%2028th%20Octovriou%20Str.%20P.O.Box%201132%20GR-57009%20KALOHORI%20GREECE%20TEL%3A2310778825%20FAX%3A2310778875%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3E%3CSTRONG%3EPlease%20help%20me%20if%20you%20know%20any%26nbsp%3Bpossibility%20to%20do%20that%20stuff.%3C%2FSTRONG%3E%20%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EAlso%20added%20an%20example%20file%20to%20make%20you%20understand%20better%20the%20data%20I%20have.%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1786009%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1786100%22%20slang%3D%22en-US%22%3ERe%3A%20Rewriting%20a%20sentence%20in%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1786100%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F161700%22%20target%3D%22_blank%22%3E%40An%C4%B1l%20Ada%C5%9F%3C%2FA%3E%26nbsp%3B%20not%20sure%20why%20you%20need%20to%20do%20this%20but%20you%20can%20create%20another%20column%20and%20use%20this%20formula%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUBSTITUTE(A2%2CCHAR(10)%2C%22%20%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehere%20is%20an%20example%20of%20how%20it%20worked%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mtarler_0-1602797969887.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F227055iE18255C75A0B34FC%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mtarler_0-1602797969887.png%22%20alt%3D%22mtarler_0-1602797969887.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Enotice%20how%20A2%20has%20the%20line%20returns%20but%20in%20B2%20it%20is%20just%20normal%20word%20wrap.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1817315%22%20slang%3D%22en-US%22%3ERe%3A%20Rewriting%20a%20sentence%20in%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1817315%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%3CBR%20%2F%3EThanks%20a%20lot%20for%20your%20reply%3CBR%20%2F%3E%3CBR%20%2F%3EI%20used%20CLEAN%20function%20and%20solved%20my%20problem%20but%20also%20tried%20yours%5B%3DSUBSTITUTE(A2%2CCHAR(10)%2C%22%20%22)%5D%20and%20it%20was%20very%20helpful%20for%20one%20of%20my%20projects%3C%2FLINGO-BODY%3E
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