How do I add leading zeroes in this example?

[Sampoline]

I want to take the original naming in the image below and turn it into the required naming format. This includes substituting "/" with "-" and then adding a leading zero to sequence number at the end of the number.

Using the 1st column, I used a simple Substitute formula to change the "/" to a "-" in the 2nd column. But I can't figure out how to add the leading zero like shown in the 3rd column. And then to make the substitution and leading zero addition work together in a single formula possibly?

 

[maabadi]

Use this formula at cell C2 for source cell B2:

=SUBSTITUTE(B2,RIGHT(B2,SEARCH("-",B2)-1),0&RIGHT(B2,SEARCH("-",B2)-1))

 

[maabadi]

I see the previous formula have problems. please use this:

=SUBSTITUTE(B2,RIGHT(B2,LEN(B2)-SEARCH("#",SUBSTITUTE(B2,"-","#",LEN(B2)-LEN(SUBSTITUTE(B2,"-",""))))),0&RIGHT(B2,LEN(B2)-SEARCH("#",SUBSTITUTE(B2,"-","#",LEN(B2)-LEN(SUBSTITUTE(B2,"-",""))))))

 

[maabadi]

Welcome to MrExcel Message Board.
Sorry I don't noticed at Previous Posts.

Or You can Use only this for Column A , Start at A2:

=SUBSTITUTE(LEFT(A2,LEN(A2)-LEN(RIGHT(A2,LEN(A2)-SEARCH("#",SUBSTITUTE(A2,"/","#",LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))))))),"/","-")&0&RIGHT(A2,LEN(A2)-SEARCH("#",SUBSTITUTE(A2,"/","#",LEN(A2)-LEN(SUBSTITUTE(A2,"/","")))))

 

[Zot]

Try
=LEFT(A2,8)&IF(ISERROR(VALUE(TEXT(RIGHT(A2,LEN(A2)-8),"00"))), "0" & RIGHT(A2,LEN(A2)-8),TEXT(RIGHT(A2,LEN(A2)-8),"00"))

 

[Sampoline]

Welcome to MrExcel Message Board.
Sorry I don't noticed at Previous Posts.

Or You can Use only this for Column A , Start at A2:

=SUBSTITUTE(LEFT(A2,LEN(A2)-LEN(RIGHT(A2,LEN(A2)-SEARCH("#",SUBSTITUTE(A2,"/","#",LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))))))),"/","-")&0&RIGHT(A2,LEN(A2)-SEARCH("#",SUBSTITUTE(A2,"/","#",LEN(A2)-LEN(SUBSTITUTE(A2,"/","")))))

Hi Maabadi, sorry for the late response. Was away for a day. This formula ALMOST works. Only thing is, I want the leading zero only for a single digit number, not double digit.

For e.g. 'A-DQ-A1-01' but NOT 'A-DQ-A1-010'; it should be 'A-DQ-A1-10'

 

[maabadi]

Sampoline, If you have fixed length before last slash "/" , then use Zot Formula, Otherwise Use this:

=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"))

 

[Sampoline]

Sampoline, If you have fixed length before last slash "/" , then use Zot Formula, Otherwise Use this:

=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"))

Zot's formula doesn't substitute "/" for "-"

And the one you posted also does not either. I need to substitute the forward slash with the hyphen and also only add the leading zero to single-digit numbers.

 

[maabadi]

Sorry I forgot to add substitiue function:
1. this is for Zot function:

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

2. this is for my Function:

=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")),"/","-")

 

[Sampoline]

Sorry I forgot to add substitiue function:
1. this is for Zot function:

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

2. this is for my Function:

=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")),"/","-")

No need to apologise! Thanks a lot for being patient to answer my questions. You're solution worked out for me.