Cell will not format numbers correctly for a 13 digit custom barc.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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. Format>Cells, on the Number tab, select
Custom, in the Type box enter 13 zeros

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
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
 
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
 
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.
 
Back
Top