I should have spotted it the first time. You need to double up the quotes. i.e.
Formula:
Range("B4") = "=LEFT(0&RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,""("",""""),"")"",""""),""+"",""""),"" "",""""),""="",""""),""-"",""""),""_"",""""),""."",""""),""?"",""""),9),3)&""-""&RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,""("",""""),"")"",""""),""+"",""""),"" "",""""),""="",""""),""-"",""""),""_"",""""),""."",""""),""?"",""""),7)"
But won't
Formula:
="0"&LEFT(A2,2)&"-"&RIGHT(A2,7)
be simpler. There's no doubt a custom format if you don;t actually want the leading zero and - in the cell value and just want to SEE the effect.
Bookmarks