Need Help Formatting a 6-digit number

[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

 

[Bob Phillips]

How about

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

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[CLR]

=IF(LEN(A1)=5,0&A1,RIGHT(A1,6))

Vaya con Dios,
Chuck, CABGx3

 

[Dave Peterson]

And one or two more...

=RIGHT(REPT("0",6)&A1,6)
or
=RIGHT("000000"&A1,6)

 

[Ron Rosenfeld]

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

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


--ron

 

[NormanHeyen]

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

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

 

[NormanHeyen]

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

Sorry, make that Format _> Cells _> Custom.

 

[Dave Peterson]

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.

 

[JessiRight77]

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

 

[Bob Phillips]

Well, you certainly had enough choices <vbg>

Bob