How to remove leading zeros in Excel

While working with numeric data in excel, specially with phone numbers, we often see the numbers with leading zeros with the numbers. There might arise a requirement when you have to remove those leading zeros from the phone numbers or any other data. 

In this tutorial we’ll learn how to remove the leading zeros from the numeric dataset shown above.

Method 1 Use Error Information 

Step 1 – Remove Leading Zeros by Using Error Information

  • The simplest of all methods is to use the Error information tool. When the numbers are stored in excel with leading zeros then by default, excel stores them as text and shows the error information as well. So select all numbers and click on the error information icon and choose the option Convert to Numbers.
  • This will convert all text to numbers removing the leading zeros as shown above.

Method 2 Use helper column multiplication method 

Step 1 – Create a helper column and multiply the data with it

  • The second method is to use a helper column consisting of digit 1 and then multiply the data column with this unity column. 
  • This will convert the text to numbers and all leading zeros will be removed as shown above.