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.

 

[DSCfromCFA]

G'day Sampoline,

I don't know why you are not getting the result you want, but you can do it in two steps without identifying the exact location of the text to be replaced.

Step 1. In cell B4 =SUBSTITUTE(A4,"/","-")

Step 2. In cell C4 =SUBSTITUTE(B4,"-","-0",3)

Cheers

shane

 

[Sampoline]

G'day Sampoline,

I don't know why you are not getting the result you want, but you can do it in two steps without identifying the exact location of the text to be replaced.

Step 1. In cell B4 =SUBSTITUTE(A4,"/","-")

Step 2. In cell C4 =SUBSTITUTE(B4,"-","-0",3)

Cheers

shane

Hi Shane,

Sorry my problem has a little more to it. Please refer to this thread to see my initial issue: How do I add leading zeroes in this example?

I don't simply need a substitution of a character. I have a huge list that will continue to update in time. So that's why I'm referencing location. And I need the leading zeroes to add as exactly above in one formula. I don't want to use the B column has a helper. I need to get the formula for C column in one go.

Also I made a small error in my screenshot. Cell C11 shoulda be A-VG-Z1-10a (only need leading zeroes for 1-9).

Thanks,
Sam

 

[Sampoline]

Just bumping this post up with a comment in case it got buried under the multitude of other questions. Thanks.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: How do I correct my formula to place the leading zero at the end of my naming (cross post)
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Sampoline]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: How do I correct my formula to place the leading zero at the end of my naming (cross post)
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

I posted in the title of that forum that it was a cross-post. And in the post I added a link to this post. Not sure what else I missed?

Anyways I'll just edit this post to reflect the same.

Cross-posted at: How do I correct my formula to place the leading zero at the end of my naming (cross post)

 

[Fluff]

Not sure what else I missed?

Whilst you mentioned it was cross posted on the other site, you did not mention it here.

 

[pjmorris]

try this: =SUBSTITUTE(SUBSTITUTE(A1,"/","/0",3),"/","-")

less is more

PS. As described by Fluff, it would help to use XL2BB to upload you example.

HTH

 

[Sampoline]

Whilst you mentioned it was cross posted on the other site, you did not mention it here.

Oh okay, sorry about that. Will keep this in mind next time.

 

[Sampoline]

try this: =SUBSTITUTE(SUBSTITUTE(A1,"/","/0",3),"/","-")

less is more

PS. As described by Fluff, it would help to use XL2BB to upload you example.

HTH

Hi pj,

I'll keep it in mind. Thanks.

Also looks like you are REALLY close. Here is a screenshot of your formula in action (represented in Column B):

The red highlighted are ones not quite right. I only want a leading zero if their value is lower than 10. So e.g. A/VG/Z1/9.1 should be A-VG-Z1-09.1 BUT A/VG/Z1/10a should be A-VG-Z1-10a.

If that makes sense. Basically 10 and above don't need a leading zero.

Thanks