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

Sampoline

New Member
Joined
Dec 2, 2020
Messages
24
Office Version
  1. 2013
Platform
  1. Windows
Hi all.

1608258863664.png


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
Joined
Feb 27, 2018
Messages
147
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 2, 2020
Messages
24
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
Dec 2, 2020
Messages
24
Office Version
  1. 2013
Platform
  1. Windows
Just bumping this post up with a comment in case it got buried under the multitude of other questions. Thanks.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,969
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Dec 2, 2020
Messages
24
Office Version
  1. 2013
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,969
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Aug 2, 2012
Messages
1,990
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Dec 2, 2020
Messages
24
Office Version
  1. 2013
Platform
  1. Windows
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):

1608517923340.png


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
 

Watch MrExcel Video

Forum statistics

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

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top