Function not giving me right output

[vmjan02]

I have this but not giving me correct results.

Golden Automation Report V1.xlsm
	B	C	D	E	F	G	H
1	MAC	Address	Address	Interface	Flags	MAC Address	Actual Outpiut
2	02:00:00:00:00:04	10.0.0.4	em1.0	none		0200-0-00-0-04	0200-0000-0004
3	32:b6:4f:16:3e:17	10.129.4.1	ae2.5	none		32b6-4-16-e-17	32b6-4f16-3e17
4	42:71:83:72:10:36	10.129.4.19	ae3.5	none		4271-8-72-0-36	4271-8372-1036
5	32:b6:4f:16:3e:17	10.129.4.81	ae2.4	none		32b6-4-16-e-17	32b6-4f16-3e17
6	42:71:83:72:10:36	10.129.4.99	ae3.4	none		4271-8-72-0-36
7	32:b6:4f:16:3e:14	10.188.170.1	fxp0.0	none		32b6-4-16-e-14
8	42:71:83:72:10:34	10.188.170.2	fxp0.0	none		4271-8-72-0-34
9	00:00:5e:00:01:5a	10.188.170.3	fxp0.0	none		0000-5-00-1-5a
10	7c:e2:ca:94:17:c1	10.188.170.4	fxp0.0	none		7ce2-c-94-7-c1
11	84:c1:c1:5f:7c:f9	10.188.170.5	fxp0.0	none		84c1-c-5f-c-f9
12	7c:25:86:0e:ee:c6	10.188.170.6	fxp0.0	none		7c25-8-0e-e-c6
13	7c:25:86:0e:ee:c6	10.188.170.7	fxp0.0	none		7c25-8-0e-e-c6
14	2c:21:31:09:d1:43	10.188.170.8	fxp0.0	none		2c21-3-09-1-43
15	30:b6:4f:0d:d2:ad	10.188.170.9	fxp0.0	none		30b6-4-0d-2-ad
16	2c:21:31:09:d1:43	10.188.170.10	fxp0.0	none		2c21-3-09-1-43
17	00:a0:a5:8a:6d:9a	10.188.170.11	fxp0.0	none		00a0-a-8a-d-9a
18	00:a0:a5:91:3b:b2	10.188.170.12	fxp0.0	none		00a0-a-91-b-b2
19	00:a0:a5:8a:6d:9a	10.188.170.13	fxp0.0	none		00a0-a-8a-d-9a
20	00:a0:a5:8a:85:26	10.188.170.14	fxp0.0	none		00a0-a-8a-5-26
21	00:a0:a5:8a:6d:62	10.188.170.15	fxp0.0	none		00a0-a-8a-d-62
22	00:a0:a5:8a:85:26	10.188.170.16	fxp0.0	none		00a0-a-8a-5-26
23	00:a0:a5:8a:55:68	10.188.170.17	fxp0.0	none		00a0-a-8a-5-68
24	00:a0:a5:8a:48:86	10.188.170.18	fxp0.0	none		00a0-a-8a-8-86
25	00:a0:a5:8a:48:86	10.188.170.19	fxp0.0	none		00a0-a-8a-8-86
26	00:a0:a5:8a:48:26	10.188.170.20	fxp0.0	none		00a0-a-8a-8-26
27	00:a0:a5:8a:47:7a	10.188.170.21	fxp0.0	none		00a0-a-8a-7-7a
Juniper INTMAC Agg

Sub JuniperINTMACWorking()

Sheets("Juniper INTMAC Agg").Select
Range("g1").Value = "MAC Address"
   
With Worksheets("Juniper INTMAC Agg")
    With .Range("G2:G" & .Range("A" & .Rows.count).End(xlUp).Row)
        .Formula = "=IF(ISERR(FIND("":"", B2)), SUBSTITUTE(TEXT(SUBSTITUTE(MID(B2,1,2)&MID(B2,4,2)&MID(B2,7,2)&MID(B2,10,2)&MID(B2,13,2)&MID(B2,16,2), "":"" ,""-""),""0000-0000-0000""), ""-0"", ""-""), TEXT(SUBSTITUTE(MID(B2,1,2)&MID(B2,4,4)&MID(B2,9,4)&MID(B2,14,4), "":"" ,""-""), ""0000-0000-0000""))"
        .Value = .Value
    End With
End With


   

End Sub

 

[RoryA]

What version(s) does it need to work on?

Unless there are variations of input that you have not shown, I would have thought:

=SUBSTITUTE(LEFT(B2,5)&"-"&MID(B2,7,5)&"-"&RIGHT(B2,5),":","")

would suffice.

 

[vmjan02]

=SUBSTITUTE(LEFT(B2,5)&"-"&MID(B2,7,5)&"-"&RIGHT(B2,5),":",""

it is right i am using 365