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

[Laudan]

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.

[Niek Otten]

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

"Laudan" <[email protected]> wrote in message
news:[email protected]...
>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.

[Duke Carey]

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.

[frankt]

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.

[Duke Carey]

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.