Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deleting LF Characters in cells
I am pasting SQL query output information from a web page (no http
address available) into excel 2003. Each of the cells from the SQL data is padded with a trailing LF (hex 0A) non-displayable "blank". How do I remove these characters. I've tried selecting alt-0010 and Char(10) in the find-replace dialog but excel says it can't find what I'm looking for. The worksheet columns all have data of varying lengths, so I can't use text-cols. I just need to remove the x'0A' characters. My imported data sometimes contains several thousand rows of multiple columns. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deleting LF Characters in cells
Edit Replace
in the Find What field touch CNTRL-j then touch Replace All -- Gary''s Student - gsnu200774 "BillH" wrote: I am pasting SQL query output information from a web page (no http address available) into excel 2003. Each of the cells from the SQL data is padded with a trailing LF (hex 0A) non-displayable "blank". How do I remove these characters. I've tried selecting alt-0010 and Char(10) in the find-replace dialog but excel says it can't find what I'm looking for. The worksheet columns all have data of varying lengths, so I can't use text-cols. I just need to remove the x'0A' characters. My imported data sometimes contains several thousand rows of multiple columns. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deleting LF Characters in cells
On Mar 19, 1:22*pm, Gary''s Student
wrote: Edit Replace in the Find What field touch CNTRL-j then touch Replace All -- Gary''s Student - gsnu200774 "BillH" wrote: I am pasting SQL query output information from a web page (no http address available) into excel 2003. *Each of the cells from the SQL data is padded with a trailing LF (hex 0A) non-displayable "blank". How do I remove these characters. *I've tried selecting alt-0010 and Char(10) in the find-replace dialog but excel says it can't find what I'm looking for. *The worksheet columns all have data of varying lengths, so I can't use text-cols. *I just need to remove the x'0A' characters. *My imported data sometimes contains several thousand rows of multiple columns.- Hide quoted text - - Show quoted text - Thanks, I tried that also, but the dialog still says it can't find the data I'm searching for. I can manually edit each cell and delete the training character, but haven't found a way to automate the process within Excel. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deleting LF Characters in cells
Did you change uncheck the "match entire cell contents" box?
Are you sure that the character is really a linefeed? If it is, then ctrl-j should work fine. If it's not, then Chip Pearson has a very nice addin that will help determine what that character(s) is: http://www.cpearson.com/excel/CellView.htm =code(right(a1,1)) may help you determine that character if it's really the last character. BillH wrote: On Mar 19, 1:22 pm, Gary''s Student wrote: Edit Replace in the Find What field touch CNTRL-j then touch Replace All -- Gary''s Student - gsnu200774 "BillH" wrote: I am pasting SQL query output information from a web page (no http address available) into excel 2003. Each of the cells from the SQL data is padded with a trailing LF (hex 0A) non-displayable "blank". How do I remove these characters. I've tried selecting alt-0010 and Char(10) in the find-replace dialog but excel says it can't find what I'm looking for. The worksheet columns all have data of varying lengths, so I can't use text-cols. I just need to remove the x'0A' characters. My imported data sometimes contains several thousand rows of multiple columns.- Hide quoted text - - Show quoted text - Thanks, I tried that also, but the dialog still says it can't find the data I'm searching for. I can manually edit each cell and delete the training character, but haven't found a way to automate the process within Excel. -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deleting LF Characters in cells
Hex 0A is definitely the CHAR(10) linfeed character so don't know why Excel
won't find it. Are you sure it is Hex 0A? There are others similar like CHAR(13) which is Hex 0D Download Chip Pearson's CellView add-in to see what is in the cells. http://www.cpearson.com/excel/CellView.aspx Gord Dibben MS Excel MVP On Wed, 19 Mar 2008 11:39:06 -0700 (PDT), BillH wrote: On Mar 19, 1:22*pm, Gary''s Student wrote: Edit Replace in the Find What field touch CNTRL-j then touch Replace All -- Gary''s Student - gsnu200774 "BillH" wrote: I am pasting SQL query output information from a web page (no http address available) into excel 2003. *Each of the cells from the SQL data is padded with a trailing LF (hex 0A) non-displayable "blank". How do I remove these characters. *I've tried selecting alt-0010 and Char(10) in the find-replace dialog but excel says it can't find what I'm looking for. *The worksheet columns all have data of varying lengths, so I can't use text-cols. *I just need to remove the x'0A' characters. *My imported data sometimes contains several thousand rows of multiple columns.- Hide quoted text - - Show quoted text - Thanks, I tried that also, but the dialog still says it can't find the data I'm searching for. I can manually edit each cell and delete the training character, but haven't found a way to automate the process within Excel. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deleting LF Characters in cells
You can check what character it is by =CODE(RIGHT(A2))
-- David Biddulph "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Hex 0A is definitely the CHAR(10) linfeed character so don't know why Excel won't find it. Are you sure it is Hex 0A? There are others similar like CHAR(13) which is Hex 0D Download Chip Pearson's CellView add-in to see what is in the cells. http://www.cpearson.com/excel/CellView.aspx Gord Dibben MS Excel MVP On Wed, 19 Mar 2008 11:39:06 -0700 (PDT), BillH wrote: On Mar 19, 1:22 pm, Gary''s Student wrote: Edit Replace in the Find What field touch CNTRL-j then touch Replace All -- Gary''s Student - gsnu200774 "BillH" wrote: I am pasting SQL query output information from a web page (no http address available) into excel 2003. Each of the cells from the SQL data is padded with a trailing LF (hex 0A) non-displayable "blank". How do I remove these characters. I've tried selecting alt-0010 and Char(10) in the find-replace dialog but excel says it can't find what I'm looking for. The worksheet columns all have data of varying lengths, so I can't use text-cols. I just need to remove the x'0A' characters. My imported data sometimes contains several thousand rows of multiple columns.- Hide quoted text - - Show quoted text - Thanks, I tried that also, but the dialog still says it can't find the data I'm searching for. I can manually edit each cell and delete the training character, but haven't found a way to automate the process within Excel. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deleting LF Characters in cells
On Mar 19, 2:30*pm, Dave Peterson wrote:
Did you change uncheck the "match entire cell contents" box? Are you sure that the character is really a linefeed? *If it is, then ctrl-j should work fine. If it's not, then Chip Pearson has a very nice addin that will help determine what that character(s) is:http://www.cpearson.com/excel/CellView.htm =code(right(a1,1)) may help you determine that character if it's really the last character. BillH wrote: On Mar 19, 1:22 pm, Gary''s Student wrote: Edit Replace in the Find What field touch CNTRL-j then touch Replace All -- Gary''s Student - gsnu200774 "BillH" wrote: I am pasting SQL query output information from a web page (no http address available) into excel 2003. *Each of the cells from the SQL data is padded with a trailing LF (hex 0A) non-displayable "blank". How do I remove these characters. *I've tried selecting alt-0010 and Char(10) in the find-replace dialog but excel says it can't find what I'm looking for. *The worksheet columns all have data of varying lengths, so I can't use text-cols. *I just need to remove the x'0A' characters. *My imported data sometimes contains several thousand rows of multiple columns.- Hide quoted text - - Show quoted text - Thanks, I tried that also, but the dialog still says it can't find the data I'm searching for. *I can manually edit each cell and delete the training character, but haven't found a way to automate the process within Excel. -- Dave Peterson- Hide quoted text - - Show quoted text - Using that code it says the character is '160'. I have used an external hex editor and it said the character is an x'0A'. Now I'm even more confused. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deleting LF Characters in cells
On Mar 19, 2:52*pm, BillH wrote:
On Mar 19, 2:30*pm, Dave Peterson wrote: Did you change uncheck the "match entire cell contents" box? Are you sure that the character is really a linefeed? *If it is, then ctrl-j should work fine. If it's not, then Chip Pearson has a very nice addin that will help determine what that character(s) is:http://www.cpearson.com/excel/CellView.htm =code(right(a1,1)) may help you determine that character if it's really the last character. BillH wrote: On Mar 19, 1:22 pm, Gary''s Student wrote: Edit Replace in the Find What field touch CNTRL-j then touch Replace All -- Gary''s Student - gsnu200774 "BillH" wrote: I am pasting SQL query output information from a web page (no http address available) into excel 2003. *Each of the cells from the SQL data is padded with a trailing LF (hex 0A) non-displayable "blank".. How do I remove these characters. *I've tried selecting alt-0010 and Char(10) in the find-replace dialog but excel says it can't find what I'm looking for. *The worksheet columns all have data of varying lengths, so I can't use text-cols. *I just need to remove the x'0A' characters. *My imported data sometimes contains several thousand rows of multiple columns.- Hide quoted text - - Show quoted text - Thanks, I tried that also, but the dialog still says it can't find the data I'm searching for. *I can manually edit each cell and delete the training character, but haven't found a way to automate the process within Excel. -- Dave Peterson- Hide quoted text - - Show quoted text - Using that code it says the character is '160'. *I have used an external hex editor and it said the character is an x'0A'. *Now I'm even more confused.- Hide quoted text - - Show quoted text - I tried Find Alt-0160 and replaced with blank and it worked! Thanks all. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deleting LF Characters in cells
Hex 160 is HTML's non-breaking space character
BillH wrote: <<snipped Using that code it says the character is '160'. I have used an external hex editor and it said the character is an x'0A'. Now I'm even more confused.- Hide quoted text - - Show quoted text - I tried Find Alt-0160 and replaced with blank and it worked! Thanks all. -- Dave Peterson |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deleting LF Characters in cells
NBSP character is DEC 160 or HEX A0
Gord On Wed, 19 Mar 2008 17:01:25 -0500, Dave Peterson wrote: Hex 160 is HTML's non-breaking space character BillH wrote: <<snipped Using that code it says the character is '160'. I have used an external hex editor and it said the character is an x'0A'. Now I'm even more confused.- Hide quoted text - - Show quoted text - I tried Find Alt-0160 and replaced with blank and it worked! Thanks all. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deleting LF Characters in cells
Oops.
Thanks for the correction. Gord Dibben wrote: NBSP character is DEC 160 or HEX A0 Gord On Wed, 19 Mar 2008 17:01:25 -0500, Dave Peterson wrote: Hex 160 is HTML's non-breaking space character BillH wrote: <<snipped Using that code it says the character is '160'. I have used an external hex editor and it said the character is an x'0A'. Now I'm even more confused.- Hide quoted text - - Show quoted text - I tried Find Alt-0160 and replaced with blank and it worked! Thanks all. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting Characters | Excel Discussion (Misc queries) | |||
Deleting rows with 11+ characters. | Excel Worksheet Functions | |||
Deleting rows with 11+ characters. | Excel Discussion (Misc queries) | |||
Deleting characters that are not numbers | Excel Discussion (Misc queries) | |||
Deleting 3 Text characters from the right | Excel Worksheet Functions |