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

 

[Peter T]

Not quite sure what you are after here. Imported (plain) text does not have
any formatting so there is no formatting to retain.

Do you want the all the text in a single cell with the 3 linefeeds (then
strip the linefeeds before uploading to SAP), or do you perhaps want text
between the linefeeds separate cells separated by two cells to simulate the
3 linefeeds, or something else.

Regards,
Peter T

 

[Guest]

Hi Peter

Yes I want the all the text in a single cell with the 3 linefeeds but remove
CHR(10)
and retain text layout. Need a macro or code to do this. ( maybe Subsitute
or replace )
Can do it manually, but need to auto it. When exported it comes over already
formatted with CHR(10)
so need to remove these and keep the text layout.
I need the strip program as you suggest.

 

[Peter T]

Yes I want the all the text in a single cell with the 3 linefeeds but

remove CHR(10)

It's the CHR(10) character in the text that instructs the line feed.
Depending on where your 3 line breaks exist, with cell wrapping and loads of
spaces inserted in the right places maybe you could simulate the same visual
text layout with no line breaks. Doable but a lot of work.

Why not simply remove the linefeeds (and replace with spaces if/as required)
before uploading to SAP.

Regards,
Peter T

Curiosity, why is it a problem for SAP to receive line breaks (and
presumably carriage returns & tabs etc)

 

[Guest]

Hi Peter
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

 

[Guest]

Hi Peter

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

 

[Peter T]

Great, thanks for letting us know

Regards,
Peter Thornton

Hi Peter

Have solved the problem

<snip>