Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Laudan
 
Posts: n/a
Default 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   Report Post  
Niek Otten
 
Posts: n/a
Default

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   Report Post  
Duke Carey
 
Posts: n/a
Default

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   Report Post  
frankt
 
Posts: n/a
Default

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   Report Post  
Duke Carey
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Format numbers in chart datatable MB Charts and Charting in Excel 3 May 29th 05 03:37 PM
How do I format a cell with numbers, text and hyphens Yaya Excel Discussion (Misc queries) 2 March 11th 05 06:33 PM
Numbers won't sort correctly. FernW Excel Discussion (Misc queries) 1 March 3rd 05 03:46 PM
how to format numbers stored as text or vice versa to use vlookup teneagle Excel Worksheet Functions 1 February 3rd 05 11:41 PM
How do I format a value when using it within a cell reference tha. packmule Excel Worksheet Functions 4 February 3rd 05 10:32 PM


All times are GMT +1. The time now is 05:09 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"