Find and replace blank characters

Every month, I download several sets of data from an internet site. The only way I can do this is to copy and paste into Excel. The problem is, that all of the cells with numbers in them show up as text, and when I press F2, I find that there is a blank space at the end of each number.

Find and replace, searching for a blank space does not work - Excel "cannot find any data to replace". That function does work on cells in which I have created my own space at the end of a number, but not on these numbers. That leads me to believe that that "blank space" is actually some other invisible character that I need to be searching for. I've attached a bit of my data - maybe somebody else can identify my phantom character.

To get around this, I have been using the formula =(left(A1,len(A1)-1), and that works, it's just that it is a pain to create another table of 20-some columns with this formula, then copy those to values, then for each column convert text to columns to change the text to values, then search and replace for #value! where the original cells were blank.

I'm not into VB. A different solution would be appreciated.

Thanks

The Char in question is 160...

You can run an Edit -> Replace on your data, in the Find What hold ALT and type 0160 using the numeric keypad
if using a laptop ALT + FN + 0160 (entering the 0160 via the numeric keypad area of the keyboard)

Leave Replace With as blank and select Replace All... you should find your values coerce to numbers automatically.

DonkeyOte
Just for interest...
wouldn't i get the same result If i type just space in find what field and nothing in replace with field?

No... Space = Char 32, Char 160 is a different character altogether.

dont wanna arque but but i made by my own way and got the same results?!...

Originally Posted by ContaminatedWitExcel

dont wanna arque but but i made by my own way and got the same results?!...

CHAR160 <> CHAR32 as Apple <> Banana...

List of ANSI codes: http://www.alanwood.net/demos/ansi.html

If you ever want to get a list for yourself...

A1: =CHAR(ROWS(A$1:A1))
copy down to A255

thx for explanation
.... i just wanted to know difference between...

exactly what character 160 is

non-breaking space