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
wrote in message
...
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

"Ashish Mathur" wrote in message
...
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

wrote in message
...
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


Cheers and many thanks for your help
Rob

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

"Ashish Mathur" wrote in message
...
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

wrote in message
...
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