Cell Format - What's wrong?

[Danny]

Hi,

I downloaded an excel spreadsheet from a website. Column A:B on the
spreadsheet is a reference for my vlookup formula in another worksheet.

The cell where my formula returns #N/A.

Say Column A5 reflects 2000.01

If I go to the specific cell (A5) and "manually type" the exact information
on the same cell (2000.01) then my formula will work!

After typing manually and my formula works referring to A5 I tried use
Column A5 as a base and use Format painter for the rest of Column A but all
the rows in column A does not work for my formula unless I type manually each
information on the cells in column A.

Please help.

 

[Dave Peterson]

Try a few functions that'll describe that cell (before you retype the value).

=istext(a1)
=isnumber(a1)
=len(a1)

My bet is that you're pasting extra characters into that cell--maybe extra
spaces or those HTML non-breaking spaces (char(160)'s).

Depending on what you find out, ...

David McRitchie has a macro that can help clean this:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

And if you're new to macros, you may want to read David's intro:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Danny]

You are absolutely right Mr. Peterson. Some are text, some are numbers but
most importantly, using =len(a1:a200), some rows comes with a different
number!

I'll go to the website you provided and hopefully, I'll be able to
"clean-up" the worksheet.

Thanks a lot.

 

[Dave Peterson]

Use a difference cell for each of the =len() formulas.

And only use a single cell in that formula:
=len(a1)
=len(a2)
.....

 

[Dave Peterson]

Use a _different_ cell for each of the =len() formulas.

(stupid fingers!)

 

[Poorvi]

There is another trick to this. Copy the entire column of numbers into a
notepad file and save it (as a txt). Then from Excel, open the txt file.
These numbers will all be in number format and you can paste it over your
original data.

Use a _different_ cell for each of the =len() formulas.

(stupid fingers!)

 

[Danny]

I went to: http://www.mvps.org/dmcritchie/excel/join.htm#trimall
The Trim All macro did it!

Thank you for your help and tips!

There is another trick to this. Copy the entire column of numbers into a
notepad file and save it (as a txt). Then from Excel, open the txt file.
These numbers will all be in number format and you can paste it over your
original data.