Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Invalid cell format in Excel
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Invalid cell format in Excel
The data is probably in Text format rather than a number format. Don't lookup:
08759 instead lookup '08759 -- Gary''s Student - gsnu200753 "MichaelRobert" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Invalid cell format in Excel
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 "MichaelRobert" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Invalid cell format in Excel
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 On Tue, 6 Nov 2007 09:33:00 -0800, MichaelRobert wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Invalid cell format in Excel
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 "MichaelRobert" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Breaking links leaving invalid reference #REF! in cell | Excel Discussion (Misc queries) | |||
cannot open excel file - says invalid file format - what to do? | Excel Discussion (Misc queries) | |||
" invalid cell references. | Excel Worksheet Functions | |||
error/ file format invalid | Excel Discussion (Misc queries) | |||
Invalid Validation if cell "Delete"d | Excel Worksheet Functions |