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

#### Sampoline

##### New Member
Hi all.

I have a formula that works out column B from column A:
Excel Formula:
``=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.

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### DSCfromCFA

##### Board Regular
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

##### New Member
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

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

#### Fluff

##### MrExcel MVP, Moderator

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.

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

##### New Member
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.

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

##### MrExcel MVP, Moderator

Not sure what else I missed?
Whilst you mentioned it was cross posted on the other site, you did not mention it here.

#### pjmorris

##### Well-known Member
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

##### New Member
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

##### New Member
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

Replies
6
Views
251
Replies
3
Views
163
Replies
3
Views
120
Replies
11
Views
414
Replies
5
Views
290

1,130,317
Messages
5,641,486
Members
417,211
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back