Pad a String then Change to Text to Replace Leading Zeros

[Nicole_Michelle01]

I have a 17-character number with leading zeros which excel cut off. The number varies but basically looks like...

00000010000100011

...where it's always 17 characters long, but the number of leading zeros preceding the first 1 varies.

in the formula bar the number looks like...

10000100011

How can I pad the string then change the format to text (') so my formula doesn't see the first one in the first position?

=SUBSTITUTE(IF(LEFT(I7)="1","1st, ","")&IF(MID(I7,2,1)="1","2nd, ","")&IF(MID(I7,3,1)="1","3rd, ","")&IF(MID(I7,4,1)="1","4th, ","")&IF(MID(I7,5,1)="1","5th, ","")&IF(MID(I7,6,1)="1","6th, ","")&IF(MID(I7,7,1)="1","7th, ","")&IF(MID(I7,8,1)="1","8th, ","")&IF(MID(I7,9,1)="1","9th, ","")&IF(MID(I7,10,1)="1","10th, ","")&IF(MID(I7,11,1)="1","11th, ","")&IF(MID(I7,12,1)="1","12th, ","")&IF(MID(I7,13,1)="1","13th, ","")&IF(MID(I7,14,1)="1","14th, ","")&IF(MID(I7,15,1)="1","15th, ","")&IF(MID(I7,16,1)="1","16th, ","")&IF(MID(I7,17,1)="1","17th, ",""),",","",LEN(SUBSTITUTE(I7,0,"")))

 

[Norie]

Does this work?

=TEXT(A1, REPT("0",17))

 

[Nicole_Michelle01]

let me try...

 

[Nicole_Michelle01]

it works, thanks!