|
Challenge - Excel Line Feed Character CHR(10) - How to Delete and keep the text formatting without going ro single line in a cell ?
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 |
Challenge - Excel Line Feed Character CHR(10) - How to Delete and
Hit Ctrl+H to launch Find/Replace dialog
Find what: CTRL + j (CTRL key + j) Replace with: a space bar (for space) or leave blank Hit Replace All. If this post helps click Yes --------------- Jacob Skaria " wrote: 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 |
Challenge - Excel Line Feed Character CHR(10) - How to Delete and keep the text formatting without going ro single line in a cell ?
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 |
Challenge - Excel Line Feed Character CHR(10) - How to Delete and keep the text formatting without going ro single line in a cell ?
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 |
Challenge - Excel Line Feed Character CHR(10) - How to Delete and keep the text formatting without going ro single line in a cell ?
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 |
Challenge - Excel Line Feed Character CHR(10) - How to Delete and keep the text formatting without going ro single line in a cell ?
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 |
Challenge - Excel Line Feed Character CHR(10) - How to Delete and keep the text formatting without going ro single line in a cell ?
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 |
| All times are GMT +1. The time now is 07:55 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com