Challenge - Excel Line Feed Character CHR(10) - How to Delete and keep the text formatting without g

[Guest]

Hi

Imported Text in cells are formatted with 3 line feed characters. If you
use clean() it deletes the characters and puts the text into one line. I
need to be able to delete the Chr(10)
and keep the formatting by a macro if possible.
This file is needed to be uploaded into SAP which doesn't like CHR(10).

Presently the only way to do this is to go to the end of each line and
manually delete the three CHR(10) and all works well as
the formatting stays the same.
I have tried Substitute and Find and replace, Clean() but it always ends up
in single
line....
Hope someone can help....
Thanks
Rob

 

[Jacob Skaria]

Hit Ctrl+H to launch Find/Replace dialog

Find what: CTRL + j (CTRL key + j)
Replace with: a space bar (for space) or leave blank
Hit Replace All.

If this post helps click Yes

 

[Ashish Mathur]

Hi,

You may also use this formula in spare column. Assumes that C5 has the
imported text


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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Guest]

Hi Ashish
This formula works but loose the text formatting as goes to one line. I need
to retain 3 line format.
cheers
Rob

 

[Guest]

Hi Ashish
This works but end up with single line... need to keep the text on separate
lines as below.
at the end of each line there is a "I" black line which I assume is Chr(10)
As mentioned I can go in and manually delete these with no problems, text
formating stays same.
This may not be able to be automated ??
I Could record a macro but the text is different depending on the contact..
Sample Text as follows

Please contact the requestor to arrange shipping. Do not contact our freight
forwarders directly.
Contact : MICHAEL MANTRA
Ph: +61 3 9999 1161
(e-mail address removed)

Cheers and many thanks for your help
Rob

 

[Ashish Mathur]

I am not clear about you question. Are you saying that you want to split
the single line contents (with two char(10) feeds) into 3 rows?

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Guest]

Hi Ashish

No the text is already formatted correctly.
The text is imported as sample shown below. I want to keep this same text
formatting but also remove CHR(10). as mentioned, doing this puts text into
one single line. The Line wrap formatting is also set on
Question is how do I remove this chr(10) and keep text formatting same.

Cheers
Rob

 

[Guest]

Hi Ashish
Have solved the problem
used code

Sub ClearChr13()
Cells.Replace What:=Chr(13), Replacement:=Chr(32), LookAt:=xlPart,
SearchOrder:=xlByRows, MatchCase:=True

End Sub

All works ok

Thanks for your help
Rob