Invalid cell format in Excel

[Guest]

I am using LOOKUP in Excel, but it cannot recognize the format of the 'lookup
value' cell which is a 5-digi ZIP code. The data in that cell is imported
from another application, and I cannot determine how it is formatted. As a
result, Lookup returns an 'n/a'.

Everything works fine if I delete the cell contents and enter the same value
manually. I have tried reformatting the cell, moving the data from 'aligned
left' to 'aligned right' with no success. I have also tried setting up an
adjoining column which is the original column multiplied by one, and that
doesn't work.

Maybe if I could see how the cell is formatted, that could give me a lead ...

Any ideas?

Thanks.

Mike

 

[Guest]

The data is probably in Text format rather than a number format. Don't lookup:
08759
instead lookup
'08759

 

[Guest]

Hi,

My guess is that your data is formatted as text and possibly with extra
spaces at the end, try this:
Select your data, then go to EDIT/REPLACE, in the FIND WHAT box put a blank
space and leave the REPLACE WITH box empty, hit REPLACE ALL, next format your
data as general.

HTH
Jean-Guy

 

[Gord Dibben]

Importing can leave extra spaces in the cell or hidden characters like the char
160 non-breaking space.

Try the Clean or Trim functions on these cells, neither of which will get rid of
char 160

Or download Chip Pearson's CellView add-in and you can see exactly what is in
these cells.

http://www.cpearson.com/excel/CellView.aspx


Gord Dibben MS Excel MVP

 

[Guest]

I appreciated the responses from Gary"s Student, pinmaster, and Gord Dibben.
I tried all the suggestions to no avail (btw, CLEAN/TRIM gave me a 'circular
reference' error message, and I was unable to get the (') added to the lookup
value past the 'error checker').

But I di find that I could get the field recognized by Lookup if I copied
the 'unreadable' column, and did a Paste-Special into a blank column using
the 'Add' option.

I am still unclear what the problem was - Chip Pearson's CellView did not
show any unusual character - but I have a solution

Thanks to all.

Mike