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

#### Rick Rothstein

##### MrExcel MVP
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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### Sampoline

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

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

Thanks.

#### pjmorris

##### Well-known Member
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
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,"/",""))),"/","-")``

#### pjmorris

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

Replies
6
Views
248
Replies
3
Views
161
Replies
3
Views
120
Replies
11
Views
414
Replies
5
Views
285

1,130,163
Messages
5,640,517
Members
417,149
Latest member
drbro

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