Best way to remove leading zeros from a non-numeric value in Excel &Convert numbers stored as text to numbers

9down votefavorite 7

I have many cells in an Excel sheet, using 9 characters of 0-9 and A-Z, that have some number of prefixed zeros: 000000123 000001DA2 0000009Q5 0000L210A 0000014A0 0000A5500 00K002200 I'd like to remove the leading zeros so that the values become: 123 1DA2 9Q5 L210A 14A0 A5500 K002200 =SUBSTITUTE(TRIM(SUBSTITUTE(B2,"0"," "))," ","0") =IF(LEFT(A1) = "0" ,RIGHT(A1, LEN(A1)-1),A1)

Convert numbers stored as text to numbers

3. Click Data > Text to Columns

On the Data tab, click Text to Columns.

4. Click Finish

Click Finish right away and Excel will convert the cells.