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


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
news:(E-Mail Removed)...
> 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
>
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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)

<(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
> news:(E-Mail Removed)...
>> 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
>>
>>
>> <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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
(E-Mail Removed)

Cheers and many thanks for your help
Rob

<(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
> 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
> news:(E-Mail Removed)...
>> 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
>>
>>
>> <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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

<(E-Mail Removed)> wrote in message
news:eDUKJ#(E-Mail Removed)...
> 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 Removed)
>
> Cheers and many thanks for your help
> Rob
>
> <(E-Mail Removed)> wrote in message
> news:#(E-Mail Removed)...
>> 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
>> news:(E-Mail Removed)...
>>> 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
>>>
>>>
>>> <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> 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

<(E-Mail Removed)> wrote in message news:%
> Hi Peter
>
> Have solved the problem
<snip>