How do I add leading zeroes in this example?

Sampoline

New Member
Joined
Dec 2, 2020
Messages
24
Office Version
  1. 2013
Platform
  1. Windows
I want to take the original naming in the image below and turn it into the required naming format. This includes substituting "/" with "-" and then adding a leading zero to sequence number at the end of the number.

Using the 1st column, I used a simple Substitute formula to change the "/" to a "-" in the 2nd column. But I can't figure out how to add the leading zero like shown in the 3rd column. And then to make the substitution and leading zero addition work together in a single formula possibly?

1606890831922.png
 
Zot's formula doesn't substitute "/" for "-"

And the one you posted also does not either. I need to substitute the forward slash with the hyphen and also only add the leading zero to single-digit numbers.
I thought you will have that helper column already ;)
 
Upvote 0

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
I thought you will have that helper column already ;)
Haha yeah I did initially. But was wondering if a formula could do it without the helper. Thanks for your solution too Zot. I'll keep this one as reference when I need to use it in conjunction with a helper column in place.
 
Upvote 0

Forum statistics

Threads
1,215,741
Messages
6,126,592
Members
449,320
Latest member
Antonino90

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
Back
Top