leading zeros

W

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!
 
P

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
 
W

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!
 
W

wiseregional

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

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
 
Top