Danny
Fri May 09 17:48:00 CDT 2008
I went to:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
> > > > > (look for "Sub Trimall()")
The Trim All macro did it!
Thank you for your help and tips!
"Poorvi" wrote:
> 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
> >