Deleting LF Characters in cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BillH - 19 Mar 2008 18:50 GMT

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.

Reply to this Message

Gary''s Student - 19 Mar 2008 19:22 GMT

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

Signature

Gary''s Student - gsnu200774

> 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
[quoted text clipped - 5 lines]
> characters. My imported data sometimes contains several thousand rows
> of multiple columns.

Reply to this Message

BillH - 19 Mar 2008 19:39 GMT

On Mar 19, 1:22 pm, Gary''s Student
<GarysStud...@discussions.microsoft.com> wrote:

> Edit > Replace >
> in the Find What field touch CNTRL-j
[quoted text clipped - 13 lines]
>
> - 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.

Reply to this Message

Dave Peterson - 19 Mar 2008 20:30 GMT

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.

> On Mar 19, 1:22 pm, Gary''s Student
> <GarysStud...@discussions.microsoft.com> wrote:
[quoted text clipped - 20 lines]
> training character, but haven't found a way to automate the process
> within Excel.

Signature

Dave Peterson

Reply to this Message

BillH - 19 Mar 2008 20:52 GMT

> Did you change uncheck the "match entire cell contents" box?
>
[quoted text clipped - 38 lines]
>
> - 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.

Reply to this Message

BillH - 19 Mar 2008 20:56 GMT

> > Did you change uncheck the "match entire cell contents" box?
>
[quoted text clipped - 44 lines]
>
> - Show quoted text -

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

Reply to this Message

Dave Peterson - 19 Mar 2008 23:01 GMT

Hex 160 is HTML's non-breaking space character

<<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
[quoted text clipped - 4 lines]
> I tried Find Alt-0160 and replaced with blank and it worked! Thanks
> all.

Signature

Dave Peterson

Reply to this Message

Gord Dibben - 19 Mar 2008 23:15 GMT

NBSP character is DEC 160 or HEX A0

Gord

>Hex 160 is HTML's non-breaking space character
>
[quoted text clipped - 7 lines]
>> I tried Find Alt-0160 and replaced with blank and it worked! Thanks
>> all.

Reply to this Message

Dave Peterson - 19 Mar 2008 23:19 GMT

Oops.

Thanks for the correction.

> NBSP character is DEC 160 or HEX A0
>
[quoted text clipped - 11 lines]
> >> I tried Find Alt-0160 and replaced with blank and it worked! Thanks
> >> all.

Signature

Dave Peterson

Reply to this Message

Gord Dibben - 19 Mar 2008 20:35 GMT

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 Mar 19, 1:22 pm, Gary''s Student
><GarysStud...@discussions.microsoft.com> wrote:
[quoted text clipped - 20 lines]
>training character, but haven't found a way to automate the process
>within Excel.

Reply to this Message

David Biddulph - 19 Mar 2008 20:51 GMT

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

Signature

David Biddulph

> Hex 0A is definitely the CHAR(10) linfeed character so don't know why
> Excel
[quoted text clipped - 34 lines]
>>training character, but haven't found a way to automate the process
>>within Excel.

Reply to this Message