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.
"Dave Peterson" wrote:
Use a _different_ cell for each of the =len() formulas.
(stupid fingers!)
Dave Peterson wrote:
Use a difference cell for each of the =len() formulas.
And only use a single cell in that formula:
=len(a1)
=len(a2)
....
Danny wrote:
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" wrote:
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 wrote:
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
--
Dave Peterson
--
Dave Peterson