Ok so I had a nice formula until a problem came along. Basically I needed to get rid off a zeros in the middle of a 10 characters String/Range i.e AB**0000**5879 to do that I have used formula SUBSTITUTE(NameRange,"0","") which gave me nice AB5879 solution. Sometimes the number at the end would only be 3 digit long AB**00000**975 so my formula would give me AB975 All great until I stumble a problem. Some of the strings came in a form of i.e. AB00004020 So my formula extracted every zero leaving me with AB42. Is there a way to extract only first four zeros in a middle an always keep the number at the and? so the last scenario would look like AB4020. Thanks in advance 

SUBSTITUTE(NameRange,"0",""))