SUBSTITUTE function

[vmjan02]

I have this data, and the functions in row 9 to 13 is not giving me the correct result as required, Actual results are in column E, can any one help to fix it.
I am trying for past 3 hrs but not able to fix it. any help will be great. Need to fix the formula in column D

Book2
	A	B	C	D	E	F
1	Short name	LT \	minus 3	Final ShotName	Actual Result
2	707-00_BYSHJZ00OL0-GPON-1/1/1/6	1	1	707-00_BYSHJZ00OL0-GPON-1/1/1/6	Correct output
3	117-00_ESKNRD00OL3-GPON-1/1/16/1	16	13	117-00_ESKNRD00OL3-GPON-1/1/13/1	Correct output
4	707-00_BYSHJZ00OL0-GPON-1/1/1/7	1	1	707-00_BYSHJZ00OL0-GPON-1/1/1/7	Correct output
5	414-00_KYBRDN00OL1-GPON-1/1/19/1	19	16	414-00_KYBRDN00OL1-GPON-1/1/16/1	Correct output
6	241-00_RABGKH00OL1-GPON-1/1/19/2	19	16	241-00_RABGKH00OL1-GPON-1/1/16/2	Correct output
7	213-00_MSLMKH00OL8-GPON-1/1/5/5	5	5	213-00_MSLMKH00OL8-GPON-1/1/5/5	Correct output
8	707-00_BYSHJZ00OL0-GPON-1/1/3/6	3	3	707-00_BYSHJZ00OL0-GPON-1/1/3/6	Correct output
9	429-00_RNUNDN00OL8-GPON-1/1/14/14	14	11	429-00_RNUNDN00OL8-GPON-1/1/11/11	429-00_RNUNDN00OL8-GPON-1/1/11/14	In this cases formula is not working correctly, need to be fixed
10	405-00_NAZITB00OLE-GPON-1/1/14/14	14	11	405-00_NAZITB00OLE-GPON-1/1/11/11	405-00_NAZITB00OLE-GPON-1/1/11/14
11	231-00_SHBRKH00OL6-GPON-1/1/14/14	14	11	231-00_SHBRKH00OL6-GPON-1/1/11/11	231-00_SHBRKH00OL6-GPON-1/1/11/14
12	701-00_KMISRRACOL1-GPON-1/1/14/14	14	11	701-00_KMISRRACOL1-GPON-1/1/11/11	701-00_KMISRRACOL1-GPON-1/1/11/14
13	213-00_MSLMKH00OL6-GPON-1/1/14/14	14	11	213-00_MSLMKH00OL6-GPON-1/1/11/11	213-00_MSLMKH00OL6-GPON-1/1/11/14
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=IF(B2>12,B2-3,B2)
D2:D13	D2	=SUBSTITUTE(A2, MID(A2, FIND("/", A2, FIND("/", A2) + 1) + 1, FIND("/", A2, FIND("/", A2, FIND("/", A2) + 1) + 1) - FIND("/", A2, FIND("/", A2) + 1) - 1), C2)

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D
1	Short name	LT \	minus 3	Final ShotName
2	707-00_BYSHJZ00OL0-GPON-1/1/1/6	1	1	707-00_BYSHJZ00OL0-GPON-1/1/1/6
3	117-00_ESKNRD00OL3-GPON-1/1/16/1	16	13	117-00_ESKNRD00OL3-GPON-1/1/13/1
4	707-00_BYSHJZ00OL0-GPON-1/1/1/7	1	1	707-00_BYSHJZ00OL0-GPON-1/1/1/7
5	414-00_KYBRDN00OL1-GPON-1/1/19/1	19	16	414-00_KYBRDN00OL1-GPON-1/1/16/1
6	241-00_RABGKH00OL1-GPON-1/1/19/2	19	16	241-00_RABGKH00OL1-GPON-1/1/16/2
7	213-00_MSLMKH00OL8-GPON-1/1/5/5	5	5	213-00_MSLMKH00OL8-GPON-1/1/5/5
8	707-00_BYSHJZ00OL0-GPON-1/1/3/6	3	3	707-00_BYSHJZ00OL0-GPON-1/1/3/6
9	429-00_RNUNDN00OL8-GPON-1/1/14/14	14	11	429-00_RNUNDN00OL8-GPON-1/1/11/14
10	405-00_NAZITB00OLE-GPON-1/1/14/14	14	11	405-00_NAZITB00OLE-GPON-1/1/11/14
11	231-00_SHBRKH00OL6-GPON-1/1/14/14	14	11	231-00_SHBRKH00OL6-GPON-1/1/11/14
12	701-00_KMISRRACOL1-GPON-1/1/14/14	14	11	701-00_KMISRRACOL1-GPON-1/1/11/14
13	213-00_MSLMKH00OL6-GPON-1/1/14/14	14	11	213-00_MSLMKH00OL6-GPON-1/1/11/14
Data

Cell Formulas
Range	Formula
C2:C8	C2	=IF(B2>12,B2-3,B2)
D2:D13	D2	=REPLACE(A2,FIND("/"&B2,A2),LEN(C2)+1,"/"&C2)

 

[vmjan02]

@Fluff
Thanks Man you are a life saver, save around 12 hrs to manual time. thanks a ton.

 

[Fluff]

You're welcome & thanks for the feedback.