Need Help Formatting a 6-digit number

  • Thread starter Thread starter jessiright77
  • Start date Start date
J

jessiright77

My excel worksheet prints a barcode sheet from data that is pulled from
a database. The format for my "Client ID" number must always be 6
digits, but the format pulled from the database is sometimes
inconsistent, and I need to find a way to correct this in my Excel
worksheet.

If the number pulled from the database is only 5 digits, I need for
Excel to add a leading 0. Likewise, if it is 7 digits, I need for
Excel to drop the first digit and retain only the right-most 6 digits.
Like so:

Database Desired Excel Format:
55555 -> 055555
0555555 -> 555555

The formula in the "Client ID" cell is as follows. (It includes a a
leading "*" before and after the number because I am printing barcode
sheets.) I thought the "000000" would format the number to 6 digits,
but it isn't working.

="*" & IF(ISNUMBER($C$5)=TRUE, TEXT(UPPER(SUBSTITUTE($C$5, " ", "!")),
"000000"), UPPER(SUBSTITUTE($C$5, " ", "!"))) & "*"

Does anyone know how I can restrict my Client ID to 6 digits... either
by dropping (or adding) the leading digit pulled from the database?

Thanks,
Jessi
 
How about

=RIGHT(REPT("0",6-MIN(6,LEN(A1)))&A1,6)

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
My excel worksheet prints a barcode sheet from data that is pulled from
a database. The format for my "Client ID" number must always be 6
digits, but the format pulled from the database is sometimes
inconsistent, and I need to find a way to correct this in my Excel
worksheet.

If the number pulled from the database is only 5 digits, I need for
Excel to add a leading 0. Likewise, if it is 7 digits, I need for
Excel to drop the first digit and retain only the right-most 6 digits.
Like so:

Database Desired Excel Format:
55555 -> 055555
0555555 -> 555555

The formula in the "Client ID" cell is as follows. (It includes a a
leading "*" before and after the number because I am printing barcode
sheets.) I thought the "000000" would format the number to 6 digits,
but it isn't working.

="*" & IF(ISNUMBER($C$5)=TRUE, TEXT(UPPER(SUBSTITUTE($C$5, " ", "!")),
"000000"), UPPER(SUBSTITUTE($C$5, " ", "!"))) & "*"

Does anyone know how I can restrict my Client ID to 6 digits... either
by dropping (or adding) the leading digit pulled from the database?

Thanks,
Jessi
Click to expand...


=TEXT(RIGHT(A1,6),"000000")


--ron
 
My excel worksheet prints a barcode sheet from data that is pulled from
a database. The format for my "Client ID" number must always be 6
digits, but the format pulled from the database is sometimes
inconsistent, and I need to find a way to correct this in my Excel
worksheet.

If the number pulled from the database is only 5 digits, I need for
Excel to add a leading 0. Likewise, if it is 7 digits, I need for
Excel to drop the first digit and retain only the right-most 6 digits.
Like so:

Database Desired Excel Format:
55555 -> 055555
0555555 -> 555555

The formula in the "Client ID" cell is as follows. (It includes a a
leading "*" before and after the number because I am printing barcode
sheets.) I thought the "000000" would format the number to 6 digits,
but it isn't working.

="*" & IF(ISNUMBER($C$5)=TRUE, TEXT(UPPER(SUBSTITUTE($C$5, " ", "!")),
"000000"), UPPER(SUBSTITUTE($C$5, " ", "!"))) & "*"

Does anyone know how I can restrict my Client ID to 6 digits... either
by dropping (or adding) the leading digit pulled from the database?

Thanks,
Jessi
Click to expand...

Can you format the cell (use the special) field and use "000000" as
the format? That should pad the leading open positions with a zero and
trim any extra (longer than 6 digits) number to 6 digits.

Norman
 
Can you format the cell (use the special) field and use "000000" as
the format? That should pad the leading open positions with a zero and
trim any extra (longer than 6 digits) number to 6 digits.

Norman
Click to expand...

Sorry, make that Format _> Cells _> Custom.
 
I think you'll find that a custom format of 000000 won't drop any digits if the
value is has more than 6 digits.
 
Thanks for all the suggestions! They were very, very helpful.

The formula that worked for my particular situation was:
="*" & RIGHT(REPT("0",6-MIN(6,LEN($C$5))) & $C$5,6) & "*"

Much obliged!

Jessi
 
Back
Top