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

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


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

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" <peter_t@discussions wrote in message
...
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


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

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)

wrote in message
...
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" <peter_t@discussions wrote in message
...
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


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

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


Cheers and many thanks for your help
Rob

wrote in message
...
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" <peter_t@discussions wrote in message
...
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


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

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

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


Cheers and many thanks for your help
Rob

wrote in message
...
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" <peter_t@discussions wrote in message
...
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


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

Great, thanks for letting us know

Regards,
Peter Thornton

wrote in message news:%
Hi Peter

Have solved the problem
<snip