Leading zero within cell string of unique identifier

[RayGraham]

I'm trying to format the result of the formula so that it includes a leading zero within the StepID. For example "101-01-01". here's the formula and a screenshot of the spreadsheet below...

=TEXT(IF(ISBLANK(A2),LEFT(I1,(SEARCH("^^",SUBSTITUTE(I1,"-","^^",LEN(I1)-LEN(SUBSTITUTE(I1,"-",""))))))&""&D2,LEFT(A2,(SEARCH("^^",SUBSTITUTE(A2,"-","^^",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))))&""&D2),"000-00-00")

 

[jtakw]

Hi,

You didn't give any details about your data in A2, D2, and I2
This assumes D2 is either Text or Custom formatted to show leading 0 (zero)
also assumes A2 and I2 is always ###-1 or 2 ##-#

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J
2	104-1-2			1					103-1-1	104-01-01
Sheet1020

Cell Formulas
Range	Formula
J2	J2	=LEFT(IF(A2="",I2,A2),4)&TEXT(MID(SUBSTITUTE(I2,"-",REPT(" ",99)),99,99),"00")&TEXT(D2,"-00")

 

[RayGraham]

That worked!! Much appreciated!

 

[jtakw]

You're welcome, thanks for the feedback.

I noticed an inconsistency in my formula, this is the Corrected version, please use this one:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J
2	104-8-2			1					103-1-1	104-08-01
Sheet1020

Cell Formulas
Range	Formula
J2	J2	=LEFT(IF(A2="",I2,A2),4)&TEXT(MID(SUBSTITUTE(IF(A2="",I2,A2),"-",REPT(" ",99)),99,99),"00")&TEXT(D2,"-00")

 

[RayGraham]

perfect!