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.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,981
Office Version
  1. 2016
Platform
  1. Windows
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...
Excel Formula:
=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,"/","")))
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Sampoline

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

1608523563183.png


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

Thanks.
 

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,990
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,981
Office Version
  1. 2016
Platform
  1. Windows
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.
Excel Formula:
=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,"/",""))),"/","-")
 
Solution

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,990
Office Version
  1. 2016
Platform
  1. Windows
This is a bit shorter, but probably not such a general solution:

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

HTH
 

Sampoline

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

Watch MrExcel Video

Forum statistics

Threads
1,130,214
Messages
5,640,912
Members
417,179
Latest member
DavidFamilytree

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