leading zeros

[wiseregional]

Help! I've got a list of NDC numbers of various lengths. I need to
convert them all to 11 digits in a 5-4-2 format (XXXXX-XXXX-XX), making
them 11 digits by adding zeros at the BEGINNING of the numer. Ex:
9999999 needs to be 00009-9999-99 and 666666666 needs to be
00666-6666-66. How can I do this? Thank you!

 

[Bob Phillips]

Add a custom format (Format>Cells>Custom) of

00000-0000-00

for those cells

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"wiseregional" <[email protected]>
wrote in message
news:[email protected]...

 

[Bernard Liengme]

With the 'bare digits' in A1
=LEFT("0000000000",11-LEN(A1))&LEFT(A1,LEN(A1)-6)&"-"&MID(A1,LEN(A1)-5,4)&"-"&RIGHT(A1,2)
gives the formatted result.
(Regardless of what NDC numbers are)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"wiseregional" <[email protected]>
wrote in message
news:[email protected]...

 

[Pete_UK]

Try this, with your data in A1 and this formula in B1:

=LEFT(REPT("0",11-LEN(A1)),5)&"-"&MID(REPT("0",11-LEN(A1),6,4)&"-"&RIGHT(A1,2)

Copy this down column B, as necessary.

Hope this helps.

Pete

 

[wiseregional]

this adds the zeros to the end...any other suggestions? thank you!!!

 

[wiseregional]

when i applied this to the numbers it gave me this message:
microsoft excel cannot calculate a formula. Cell references in the
formula refer to the formula's result, creating a circular reference.
What am I doing wrong???
THANK YOU!

 

[Bob Phillips]

Adds them at the front for me

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"wiseregional" <[email protected]>
wrote in message

 

[wiseregional]

I WAS USING #####-####-## INSTEAD OF ZEROS! IT WORKS NOW!!! THANK YOU
SOOOOO MUCH!

 

[Zack Barresse]

You can't use formulas as custom cell formats.

 

[MDBJ]

you are doing this in cell a1?

what you do, using the example below, is put a # in a1 like 555
then you put the formula below anywhere BUT a1

"wiseregional" <[email protected]>
wrote in message