Excel Won't Recognize Numbers - Can't delete space before data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I can't get Excel to recognize numbers when copying text from a specific
site for some reason. When I copy and paste numbers and search terms after
doing a query from this site...

http://inventory.overture.com/d/searchinventory/suggestion/

it seems that in excel, they display with a space before each number and
each term. I'm not an advanced user and I just can't seem to get rid of the
space so that I can sort by number or by term alphbetically (without manually
deleting each unnecessary space). I have tried using the 'trim' function,
paste special->values, format cells->number, replacing the space with
nothing, etc. but none of these things seems to work. Sometimes I have
hundreds of these values that I need to work with so manually selecting cells
and deleting the first character of each is an arduous process to say the
least.

Can someone please help me?

Thanks,
Chris
 
Chris from Boston wrote...
I can't get Excel to recognize numbers when copying text from a specific
site for some reason. When I copy and paste numbers and search terms after
doing a query from this site... ....
it seems that in excel, they display with a space before each number and
each term. I'm not an advanced user and I just can't seem to get rid of the
space so that I can sort by number or by term alphbetically (without manually
deleting each unnecessary space). I have tried using the 'trim' function,
paste special->values, format cells->number, replacing the space with
nothing, etc. but none of these things seems to work. Sometimes I have
hundreds of these values that I need to work with so manually selecting cells
and deleting the first character of each is an arduous process to say the
least.
Click to expand...

Almost certainly you're dealing with nonbreaking HTML spaces (decimal
char code 160) rather than ASCII (breaking?) spaces (decimal char code
32). The simplest way to handle them is to type the formula =CHAR(160)
in a blank cell, press [F9] rather than enter, press [Ctrl]+C to copy
the nonbreaking space to the clipboard, and press [Esc]. Then select
the entire range containing these imported numbers, run the menu
command Edit > Replace, clear the Find what field, press [Ctrl]+V to
paste the nonbreaking space into that field, clear the Replace with
field, and click on the Replace all button.
 
Thanks a lot Harlan! I would have never figured that out on my own.


Harlan Grove said:
Chris from Boston wrote...
I can't get Excel to recognize numbers when copying text from a specific
site for some reason. When I copy and paste numbers and search terms after
doing a query from this site... ....
it seems that in excel, they display with a space before each number and
each term. I'm not an advanced user and I just can't seem to get rid of the
space so that I can sort by number or by term alphbetically (without manually
deleting each unnecessary space). I have tried using the 'trim' function,
paste special->values, format cells->number, replacing the space with
nothing, etc. but none of these things seems to work. Sometimes I have
hundreds of these values that I need to work with so manually selecting cells
and deleting the first character of each is an arduous process to say the
least.
Click to expand...

Almost certainly you're dealing with nonbreaking HTML spaces (decimal
char code 160) rather than ASCII (breaking?) spaces (decimal char code
32). The simplest way to handle them is to type the formula =CHAR(160)
in a blank cell, press [F9] rather than enter, press [Ctrl]+C to copy
the nonbreaking space to the clipboard, and press [Esc]. Then select
the entire range containing these imported numbers, run the menu
command Edit > Replace, clear the Find what field, press [Ctrl]+V to
paste the nonbreaking space into that field, clear the Replace with
field, and click on the Replace all button.
Click to expand...
 
Same concept, but perhaps a "little" simpler:

Select all the cells with those imported numbers.
Then,
<Edit> <Replace>

In the "Find What" box,
Hold <Alt> and type.
0160
Using the numbers from the Num keypad, *not* the ones under the function
keys.

Don't worry about not seeing anything in the "Find What" box, you ARE
entering a space, don 't forget!

Leave the "Replace With" box empty, and then click "Replace All".

This will eliminate all the Char(160) spaces.


HTH

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------




Chris from Boston said:
Thanks a lot Harlan! I would have never figured that out on my own.


Harlan Grove said:
Chris from Boston wrote...
I can't get Excel to recognize numbers when copying text from a specific
site for some reason. When I copy and paste numbers and search terms after
doing a query from this site... ....
it seems that in excel, they display with a space before each number and
each term. I'm not an advanced user and I just can't seem to get rid of the
space so that I can sort by number or by term alphbetically (without manually
deleting each unnecessary space). I have tried using the 'trim' function,
paste special->values, format cells->number, replacing the space with
nothing, etc. but none of these things seems to work. Sometimes I have
hundreds of these values that I need to work with so manually selecting cells
and deleting the first character of each is an arduous process to say the
least.
Click to expand...

Almost certainly you're dealing with nonbreaking HTML spaces (decimal
char code 160) rather than ASCII (breaking?) spaces (decimal char code
32). The simplest way to handle them is to type the formula =CHAR(160)
in a blank cell, press [F9] rather than enter, press [Ctrl]+C to copy
the nonbreaking space to the clipboard, and press [Esc]. Then select
the entire range containing these imported numbers, run the menu
command Edit > Replace, clear the Find what field, press [Ctrl]+V to
paste the nonbreaking space into that field, clear the Replace with
field, and click on the Replace all button.
Click to expand...
Click to expand...
 
RagDyer wrote...
Same concept, but perhaps a "little" simpler:

Select all the cells with those imported numbers.
Then,
<Edit> <Replace>

In the "Find What" box,
Hold <Alt> and type.
0160
Using the numbers from the Num keypad, *not* the ones under the function
keys.
Click to expand...
....

Agreed, this is simpler . . . except when using laptops. It's difficult
to the point of absurdity on some laptops to key in characters by
decimal code.
 
This won't help with the alt-0160, but ctrl-j will do the same as alt-0010.

(I find that easier even on a full sized keyboard.)
 
Back
Top