Excel Fox
04-04-2011, 01:16 AM
=SUBSTITUTE(A1,"0","") 'To remove all 0s from the text
=MID(A1,MIN(IF(ISERROR(SEARCH({1,2,3,4,5,6,7,8,9}, A1)),"",SEARCH({1,2,3,4,5,6,7,8,9},A1))),255) confirmed as an array formula using CTRL+SHIFT+ENTER 'To remove only the leading 0s (Excel 2003 and Less)
=MID(A1,MIN(IFERROR(SEARCH({1,2,3,4,5,6,7,8,9},A1) ,"")),255) confirmed as an array formula using CTRL+SHIFT+ENTER 'To remove only the leading 0s (Excel 2007 & 2010)
where Range A1 contains an alphanumeric text
=MID(A1,MIN(IF(ISERROR(SEARCH({1,2,3,4,5,6,7,8,9}, A1)),"",SEARCH({1,2,3,4,5,6,7,8,9},A1))),255) confirmed as an array formula using CTRL+SHIFT+ENTER 'To remove only the leading 0s (Excel 2003 and Less)
=MID(A1,MIN(IFERROR(SEARCH({1,2,3,4,5,6,7,8,9},A1) ,"")),255) confirmed as an array formula using CTRL+SHIFT+ENTER 'To remove only the leading 0s (Excel 2007 & 2010)
where Range A1 contains an alphanumeric text