@ > Home > Contents > Binary Replace of leading fillers (instead of sequential) Excel 97+

Replace up to 15 leading zeros in a string: A1: "000000000000000as000g".

Solution:

B1: =SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( "#"&A1, "#00000000", "#"), "#0000", "#"), "#00", "#"), "#0", "#"), "#",)

resulting to "as000g". Note: It even works with Pocket Excel. You can enhance it up to 127 leading zeros as follows (as a limit before xl2007):

B1: =SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( "#"&A1, "#0000000000000000000000000000000000000000000000000000000000000000", "#"), "#00000000000000000000000000000000", "#"), "#0000000000000000", "#"), "#00000000", "#"), "#0000", "#"), "#00", "#"), "#0", "#"), "#",)

Oh yes, I know. Use REPT("0",64) etc for shortening it. But I prefer to have the final 8th nesting level for more zeros instead. For a limit of some systems of no more than 255 characters per formula, you have to use one level less (63 zeros).

Of course you may replace 0 and # with other characters.

A sequential replace you find here (Source: http://excelformeln.de/formeln.html?welcher=331, translated array formula):
B1: {=MID(A1,MATCH(TRUE,MID(A1,COLUMN(\$1:\$1),1)<>"0",0),99)}

So, why binary replace? It only uses the logarithm of the count of sequential replaces, in theory. In reality, a cell function uses fixed structures:

Sequential - 255 loops - replacing up to 255 zeros
Binary - 8 nested functions - replacing up to 127 zeros

The later might be considerably faster when converting mass data. Since it uses A1 only once instead of twice, it works well with 5043.htm.

VBA solution (might be done better):

Function NoLeadingZero(s As String) As String
p\$ = "#"
i& = Log(Len(s)) / Log(2#)
For j& = i& To 0 Step -1
s = Replace(p\$ & s, p\$ & String(2 ^ j&, "0"), "")
Next