What am I missing in my formula for adding leading zeroes to my list?

[Sampoline]

Hi all.

I have a formula that works out column B from column A:

=SUBSTITUTE(LEFT(A2,LEN(A2)-LEN(RIGHT(A2,LEN(A2)-SEARCH("#",SUBSTITUTE(A2,"/","#",LEN(A2)-LEN(SUBSTITUTE(A2,"/","")))))))&IF(ISERROR(VALUE(TEXT(RIGHT(A2,LEN(A2)-(LEN(A2)-LEN(RIGHT(A2,LEN(A2)-SEARCH("#",SUBSTITUTE(A2,"/","#",LEN(A2)-LEN(SUBSTITUTE(A2,"/","")))))))),"00"))), "0" & RIGHT(A2,LEN(A2)-(LEN(A2)-LEN(RIGHT(A2,LEN(A2)-SEARCH("#",SUBSTITUTE(A2,"/","#",LEN(A2)-LEN(SUBSTITUTE(A2,"/","")))))))),TEXT(RIGHT(A2,LEN(A2)-(LEN(A2)-LEN(RIGHT(A2,LEN(A2)-SEARCH("#",SUBSTITUTE(A2,"/","#",LEN(A2)-LEN(SUBSTITUTE(A2,"/","")))))))),"00")),"/","-")

It works for the most part, except as shown by the red highlight. In Column C, the green highlighted cell is supposed to be the correct result. The formula I have above doesn't do this.

Can someone help me fix the formula?

Thanks.

 

[Rick Rothstein]

Do you realize that in your first message you showed a leading zero for your A/VG/Z1/10a example? Anyway, give this formula a try...

=SUBSTITUTE(A2,"/",IF(LOOKUP(9.9E+307,--LEFT(TRIM(RIGHT(SUBSTITUTE(A2,"/",REPT(" ",99)),99)),ROW($1:$99)))<10,"/0","/"),LEN(A2)-LEN(SUBSTITUTE(A2,"/","")))

 

[Sampoline]

Do you realize that in your first message you showed a leading zero for your A/VG/Z1/10a example? Anyway, give this formula a try...

=SUBSTITUTE(A2,"/",IF(LOOKUP(9.9E+307,--LEFT(TRIM(RIGHT(SUBSTITUTE(A2,"/",REPT(" ",99)),99)),ROW($1:$99)))<10,"/0","/"),LEN(A2)-LEN(SUBSTITUTE(A2,"/","")))

My apologies Rick, you are right. My mistake.

With your formula, this is how it shows up:

Yep that fixed the leading zero issue, but I still need to substitute "/" for "-" then this will be perfect.

Thanks.

 

[pjmorris]

That’s ent entirely different question, and not what you showed in your original question! It’s entirely possible, but will have to wait until I get to a computer

 

[Rick Rothstein]

I was so intent on fixing the leading zero problem that I forgot about the dashes. It's a simple fix... all we have to do is put another SUBSTITUTE function call around what I posted.

=SUBSTITUTE(SUBSTITUTE(A2,"/",IF(LOOKUP(9.9E+307,--LEFT(TRIM(RIGHT(SUBSTITUTE(A2,"/",REPT(" ",99)),99)),ROW($1:$99)))<10,"/0","/"),LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))),"/","-")

 

[pjmorris]

This is a bit shorter, but probably not such a general solution:

=SUBSTITUTE(  IF(  ISERROR(1*MID(A1,10,1)),  SUBSTITUTE(A1,"/","/0",3),  A1),  "/",  "-")

HTH

 

[Sampoline]

I was so intent on fixing the leading zero problem that I forgot about the dashes. It's a simple fix... all we have to do is put another SUBSTITUTE function call around what I posted.

=SUBSTITUTE(SUBSTITUTE(A2,"/",IF(LOOKUP(9.9E+307,--LEFT(TRIM(RIGHT(SUBSTITUTE(A2,"/",REPT(" ",99)),99)),ROW($1:$99)))<10,"/0","/"),LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))),"/","-")

Thanks for your help Rick, and cheers for yours too PJ. This was the right solution for me.

Much appreciated!