Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Cell will not format numbers correctly for a 13 digit custom barc.
I have a column of 13 digit barcode numbers. I have a custom format to make
sure that all numbers are 13 digits. I have a lookup to the barcode cell which worked the first time but after updating the query, I get n/a's in the lookup column. The cells show 2.1E+12 which is the format for number 12 digits or longer. I can make the lookup read the cell if I put a ' in the cell but then I will have to do this to all of the cells in the column and also add a 0 where necessary to make the barcode 13 digits. Also if I change one of the cells using the ' when I use the paintbrush, it does not update the format of the other cells. |
#2
|
|||
|
|||
Probably your search argument is text, rather than a number. This often
happens with "numbers" from other sources. Multiply by 1 and you get a number. FormatCells, on the Number tab, select Custom, in the Type box enter 13 zeros -- Kind Regards, Niek Otten Microsoft MVP - Excel "Laudan" wrote in message ... I have a column of 13 digit barcode numbers. I have a custom format to make sure that all numbers are 13 digits. I have a lookup to the barcode cell which worked the first time but after updating the query, I get n/a's in the lookup column. The cells show 2.1E+12 which is the format for number 12 digits or longer. I can make the lookup read the cell if I put a ' in the cell but then I will have to do this to all of the cells in the column and also add a 0 where necessary to make the barcode 13 digits. Also if I change one of the cells using the ' when I use the paintbrush, it does not update the format of the other cells. |
#3
|
|||
|
|||
If you have a 12 digit number that is formatted to show a leading zeros, how
are you trying to find it with the VLOOKUP()? With the 12 actual digits, or with a text string of 12 digits and a leading zero. The former will work, but the latter will not. You may want to convert all the numbers to text in a helper column with =TEXT(number, "0000000000000") Then you can use text strings for your lookup values "Laudan" wrote: I have a column of 13 digit barcode numbers. I have a custom format to make sure that all numbers are 13 digits. I have a lookup to the barcode cell which worked the first time but after updating the query, I get n/a's in the lookup column. The cells show 2.1E+12 which is the format for number 12 digits or longer. I can make the lookup read the cell if I put a ' in the cell but then I will have to do this to all of the cells in the column and also add a 0 where necessary to make the barcode 13 digits. Also if I change one of the cells using the ' when I use the paintbrush, it does not update the format of the other cells. |
#4
|
|||
|
|||
The prior responses are in the correct ballpark. Depending on your version
the lookup value can be text or numbers. Since these are barcodes you should convert both to text. The easy way to do this is to select all the numbers, click "Data" from the top menu, click "Text to Columns" and bring up the Wizard. Choose "Delimited and click "Next" two times. In the last dialog choose "Text" then click "Finnish". All values will now be text strings. This is easier than using the TEXT function though that is valid also. Frank "Duke Carey" wrote: If you have a 12 digit number that is formatted to show a leading zeros, how are you trying to find it with the VLOOKUP()? With the 12 actual digits, or with a text string of 12 digits and a leading zero. The former will work, but the latter will not. You may want to convert all the numbers to text in a helper column with =TEXT(number, "0000000000000") Then you can use text strings for your lookup values "Laudan" wrote: I have a column of 13 digit barcode numbers. I have a custom format to make sure that all numbers are 13 digits. I have a lookup to the barcode cell which worked the first time but after updating the query, I get n/a's in the lookup column. The cells show 2.1E+12 which is the format for number 12 digits or longer. I can make the lookup read the cell if I put a ' in the cell but then I will have to do this to all of the cells in the column and also add a 0 where necessary to make the barcode 13 digits. Also if I change one of the cells using the ' when I use the paintbrush, it does not update the format of the other cells. |
#5
|
|||
|
|||
Text to columns is a great idea, but if cels contain shorter numbers
formatted to show 13 digits, the OP will not get what he wants without the TEXT() function. "frankt" wrote: The prior responses are in the correct ballpark. Depending on your version the lookup value can be text or numbers. Since these are barcodes you should convert both to text. The easy way to do this is to select all the numbers, click "Data" from the top menu, click "Text to Columns" and bring up the Wizard. Choose "Delimited and click "Next" two times. In the last dialog choose "Text" then click "Finnish". All values will now be text strings. This is easier than using the TEXT function though that is valid also. Frank "Duke Carey" wrote: If you have a 12 digit number that is formatted to show a leading zeros, how are you trying to find it with the VLOOKUP()? With the 12 actual digits, or with a text string of 12 digits and a leading zero. The former will work, but the latter will not. You may want to convert all the numbers to text in a helper column with =TEXT(number, "0000000000000") Then you can use text strings for your lookup values "Laudan" wrote: I have a column of 13 digit barcode numbers. I have a custom format to make sure that all numbers are 13 digits. I have a lookup to the barcode cell which worked the first time but after updating the query, I get n/a's in the lookup column. The cells show 2.1E+12 which is the format for number 12 digits or longer. I can make the lookup read the cell if I put a ' in the cell but then I will have to do this to all of the cells in the column and also add a 0 where necessary to make the barcode 13 digits. Also if I change one of the cells using the ' when I use the paintbrush, it does not update the format of the other cells. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format numbers in chart datatable | Charts and Charting in Excel | |||
How do I format a cell with numbers, text and hyphens | Excel Discussion (Misc queries) | |||
Numbers won't sort correctly. | Excel Discussion (Misc queries) | |||
how to format numbers stored as text or vice versa to use vlookup | Excel Worksheet Functions | |||
How do I format a value when using it within a cell reference tha. | Excel Worksheet Functions |