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.

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.

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.

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

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.

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.

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.

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.

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

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.

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