Cell Format - What's wrong?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Danny - 08 May 2008 22:51 GMT

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.

Reply to this Message

Dave Peterson - 09 May 2008 00:05 GMT

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

> Hi,
>
[quoted text clipped - 14 lines]
>
> Please help.

Signature

Dave Peterson

Reply to this Message

Danny - 09 May 2008 00:36 GMT

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.

> Try a few functions that'll describe that cell (before you retype the value).
>
[quoted text clipped - 32 lines]
> >
> > Please help.

Reply to this Message

Dave Peterson - 09 May 2008 01:27 GMT

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

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

> 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
[quoted text clipped - 45 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Dave Peterson - 09 May 2008 01:33 GMT

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

(stupid fingers!)

> Use a difference cell for each of the =len() formulas.
>
[quoted text clipped - 56 lines]
>
> Dave Peterson

Signature

Dave Peterson

Reply to this Message

Poorvi - 09 May 2008 18:56 GMT

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.
>
[quoted text clipped - 60 lines]
> >
> > Dave Peterson

Reply to this Message

Danny - 09 May 2008 23:48 GMT

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!

> 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.
[quoted text clipped - 65 lines]
> > >
> > > Dave Peterson

Reply to this Message