Excel Line Feed Character - How to delete the CHR(10) and keep the formatting without going to singl

[Guest]

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 but it always ends up in single
line....
Hope someone can help....

Thanks
Rob

 

[macropod]

Hi washley51,

The Chr(10) forces a line-break at a specified point and you can't exactly reproduce that in with text wrapping alone. You can,
though, use Chr(160) to keep different words together (whether your other application will accept this is another matter, though).
For many purposes, the result of the first measure alone is 'near enough'. Together, these measure can give results visually the
same as what you get with Chr(10).

 

[Guest]

Hi Macropod
Many thanks for your reply.
What I need is to be able to use the replace command or substitute or
deleted the CHR(10) character in macro if possible.
It can be done manually without any issues, but as the column could have
quite a few items I need to find a solution.
Maybe a bit of code that can go to the end of each line, go past the Chr(10)
, Backspace to delete it, page down arrow to the next
line , backspace to delete CHR(10), and repeat for next line as well. The
format of text stays the same when manually doing this.
There must be a way to do this... trouble is the text could vary line to
line in length.

Cheers
Robin

Hi washley51,

The Chr(10) forces a line-break at a specified point and you can't exactly
reproduce that in with text wrapping alone. You can, though, use Chr(160)
to keep different words together (whether your other application will
accept this is another matter, though). For many purposes, the result of
the first measure alone is 'near enough'. Together, these measure can give
results visually the same as what you get with Chr(10).

--
Cheers
macropod
[Microsoft MVP - Word]

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 but it always ends up in
single line....
Hope someone can help....

Thanks
Rob

 

[macropod]

Hi Rob,

You could use Find/Replace, inserting Chr 10 (Alt-010) into the Find box. If you have trouble inputting that, you coulduse the
following macro:
Sub ClearChr10()
Cells.Replace What:=Chr(10), Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True
End Sub

--
Cheers
macropod
[Microsoft MVP - Word]

Hi Macropod
Many thanks for your reply.
What I need is to be able to use the replace command or substitute or deleted the CHR(10) character in macro if possible.
It can be done manually without any issues, but as the column could have quite a few items I need to find a solution.
Maybe a bit of code that can go to the end of each line, go past the Chr(10) , Backspace to delete it, page down arrow to the
next
line , backspace to delete CHR(10), and repeat for next line as well. The format of text stays the same when manually doing this.
There must be a way to do this... trouble is the text could vary line to line in length.

Cheers
Robin

Hi washley51,

The Chr(10) forces a line-break at a specified point and you can't exactly reproduce that in with text wrapping alone. You can,
though, use Chr(160) to keep different words together (whether your other application will accept this is another matter,
though). For many purposes, the result of the first measure alone is 'near enough'. Together, these measure can give results
visually the same as what you get with Chr(10).

--
Cheers
macropod
[Microsoft MVP - Word]

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 but it always ends up in single line....
Hope someone can help....

Thanks
Rob

 

[Guest]

Hi Macropod

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

Hi Rob,

You could use Find/Replace, inserting Chr 10 (Alt-010) into the Find box.
If you have trouble inputting that, you coulduse the following macro:
Sub ClearChr10()
Cells.Replace What:=Chr(10), Replacement:="", LookAt:=xlPart,
SearchOrder:=xlByRows, MatchCase:=True
End Sub

--
Cheers
macropod
[Microsoft MVP - Word]

Hi Macropod
Many thanks for your reply.
What I need is to be able to use the replace command or substitute or
deleted the CHR(10) character in macro if possible.
It can be done manually without any issues, but as the column could have
quite a few items I need to find a solution.
Maybe a bit of code that can go to the end of each line, go past the
Chr(10) , Backspace to delete it, page down arrow to the next
line , backspace to delete CHR(10), and repeat for next line as well.
The format of text stays the same when manually doing this.
There must be a way to do this... trouble is the text could vary line to
line in length.

Cheers
Robin

Hi washley51,

The Chr(10) forces a line-break at a specified point and you can't
exactly reproduce that in with text wrapping alone. You can, though, use
Chr(160) to keep different words together (whether your other
application will accept this is another matter, though). For many
purposes, the result of the first measure alone is 'near enough'.
Together, these measure can give results visually the same as what you
get with Chr(10).

--
Cheers
macropod
[Microsoft MVP - Word]


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 but it always ends up in
single line....
Hope someone can help....

Thanks
Rob

 

[macropod]

Hi washley51,

Do the cells concerned have text-wrapping switched on?

--
Cheers
macropod
[Microsoft MVP - Word]

Hi Macropod

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

Hi Rob,

You could use Find/Replace, inserting Chr 10 (Alt-010) into the Find box.
If you have trouble inputting that, you coulduse the following macro:
Sub ClearChr10()
Cells.Replace What:=Chr(10), Replacement:="", LookAt:=xlPart,
SearchOrder:=xlByRows, MatchCase:=True
End Sub

--
Cheers
macropod
[Microsoft MVP - Word]

Hi Macropod
Many thanks for your reply.
What I need is to be able to use the replace command or substitute or
deleted the CHR(10) character in macro if possible.
It can be done manually without any issues, but as the column could have
quite a few items I need to find a solution.
Maybe a bit of code that can go to the end of each line, go past the
Chr(10) , Backspace to delete it, page down arrow to the next
line , backspace to delete CHR(10), and repeat for next line as well.
The format of text stays the same when manually doing this.
There must be a way to do this... trouble is the text could vary line to
line in length.

Cheers
Robin

Hi washley51,

The Chr(10) forces a line-break at a specified point and you can't
exactly reproduce that in with text wrapping alone. You can, though, use
Chr(160) to keep different words together (whether your other
application will accept this is another matter, though). For many
purposes, the result of the first measure alone is 'near enough'.
Together, these measure can give results visually the same as what you
get with Chr(10).

--
Cheers
macropod
[Microsoft MVP - Word]


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 but it always ends up in
single line....
Hope someone can help....

Thanks
Rob

 

[Guest]

Hi

Yes this is switched on

thanks
Rob

Hi washley51,

Do the cells concerned have text-wrapping switched on?

--
Cheers
macropod
[Microsoft MVP - Word]

Hi Macropod

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

Hi Rob,

You could use Find/Replace, inserting Chr 10 (Alt-010) into the Find
box. If you have trouble inputting that, you coulduse the following
macro:
Sub ClearChr10()
Cells.Replace What:=Chr(10), Replacement:="", LookAt:=xlPart,
SearchOrder:=xlByRows, MatchCase:=True
End Sub

--
Cheers
macropod
[Microsoft MVP - Word]


Hi Macropod
Many thanks for your reply.
What I need is to be able to use the replace command or substitute or
deleted the CHR(10) character in macro if possible.
It can be done manually without any issues, but as the column could
have quite a few items I need to find a solution.
Maybe a bit of code that can go to the end of each line, go past the
Chr(10) , Backspace to delete it, page down arrow to the next
line , backspace to delete CHR(10), and repeat for next line as well.
The format of text stays the same when manually doing this.
There must be a way to do this... trouble is the text could vary line
to line in length.

Cheers
Robin

Hi washley51,

The Chr(10) forces a line-break at a specified point and you can't
exactly reproduce that in with text wrapping alone. You can, though,
use Chr(160) to keep different words together (whether your other
application will accept this is another matter, though). For many
purposes, the result of the first measure alone is 'near enough'.
Together, these measure can give results visually the same as what you
get with Chr(10).

--
Cheers
macropod
[Microsoft MVP - Word]


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 but it always ends up in
single line....
Hope someone can help....

Thanks
Rob

 

[Guest]

Hi Macropod
Have finally solved the problem with the help with your code.

used macro

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

End Sub

All works ok
Many thanks
Rob

Hi

Yes this is switched on

thanks
Rob

Hi washley51,

Do the cells concerned have text-wrapping switched on?

--
Cheers
macropod
[Microsoft MVP - Word]

Hi Macropod

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


Hi Rob,

You could use Find/Replace, inserting Chr 10 (Alt-010) into the Find
box. If you have trouble inputting that, you coulduse the following
macro:
Sub ClearChr10()
Cells.Replace What:=Chr(10), Replacement:="", LookAt:=xlPart,
SearchOrder:=xlByRows, MatchCase:=True
End Sub

--
Cheers
macropod
[Microsoft MVP - Word]


Hi Macropod
Many thanks for your reply.
What I need is to be able to use the replace command or substitute or
deleted the CHR(10) character in macro if possible.
It can be done manually without any issues, but as the column could
have quite a few items I need to find a solution.
Maybe a bit of code that can go to the end of each line, go past the
Chr(10) , Backspace to delete it, page down arrow to the next
line , backspace to delete CHR(10), and repeat for next line as well.
The format of text stays the same when manually doing this.
There must be a way to do this... trouble is the text could vary line
to line in length.

Cheers
Robin

Hi washley51,

The Chr(10) forces a line-break at a specified point and you can't
exactly reproduce that in with text wrapping alone. You can, though,
use Chr(160) to keep different words together (whether your other
application will accept this is another matter, though). For many
purposes, the result of the first measure alone is 'near enough'.
Together, these measure can give results visually the same as what
you get with Chr(10).

--
Cheers
macropod
[Microsoft MVP - Word]


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 but it always ends up
in single line....
Hope someone can help....

Thanks
Rob