Deleting LF Characters in cells

[BillH]

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.

 

[Gary''s Student]

Edit > Replace >
in the Find What field touch CNTRL-j
then touch Replace All

 

[BillH]

Edit > Replace >
in the Find What field touch CNTRL-j
then touch Replace All
--
Gary''s Student - gsnu200774





- 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]

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.

 

[Gord Dibben]

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

 

[David Biddulph]

You can check what character it is by =CODE(RIGHT(A2))

 

[BillH]

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.






--

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.

 

[BillH]

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]

Hex 160 is HTML's non-breaking space character

I tried Find Alt-0160 and replaced with blank and it worked! Thanks
all.

 

[Gord Dibben]

NBSP character is DEC 160 or HEX A0


Gord

Hex 160 is HTML's non-breaking space character

 

[Dave Peterson]

Oops.

Thanks for the correction.

NBSP character is DEC 160 or HEX A0

Gord