7 Quick Hacks: How to Remove Leading Zeros in Excel Like a Pro?

Recently many users have reported that when they were dealing with the lots of data in the spreadsheets, Excel was unable to delete such leading zeros. Although these zeros are useless, it will be better to delete those leading zeros in Excel. In this blog, we will delve into the nuances of such zeros and discover easy solutions on how to remove leading zeros in Excel.

So, let’s get started…

Understanding Leading Zeros in MS Excel

Leading zeros are those zeros that come before the numerical value in the cell of a spreadsheet. Such zeros often sneak into the datasets throughout numerous data entry processes, they can alter calculations & lead to erroneous results.

In simple words, the leading zeros in Excel refer to zeros which appear before the numeric digits but have no value.

For e.g., if you type 000627981, it will appear as 627981 by default (see the below image). It means Excel removes the leading zeros before numbers in order to improve readability.

How to Remove Leading Zeros in Excel 2016/2013/2010?

Follow the below step-by-step methods for how to remove leading numbers in Excel.

Way 1- Convert Text to the Number

The very first method that I would like to suggest is to convert the text to number. Sometimes the numeric values in your Excel spreadsheet don’t perform like numbers & they may even generate peculiar errors.

However, the main reason behind this is- the numbers in the cells are formatted as text. So, it is suggested to convert the text to number to delete such unnecessary zeros.

Here’s how you can do so:

Step 1- Firstly, arrange the dataset. (As shown in the below img.)

Step 2- After this, choose the option “Convert to Number” from an error mark.

Step 3- Lastly, you’ll get the wanted result.

Way 2- Apply VBA Code

VBA (Visual Basic for Applications) is a macro component that users use to customize & automate the Excel. So, here you can also apply the VBA macro to remove trailing zeros in Excel.

Follow the below steps to apply the VBA code in Excel and remove the unnecessary zeros:

Step 1- Go to the Developer tab then click on Insert option >> Form Controls.

Step 2- Now, you’ll get the result like shown below.

Step 3- Then, right-click on a button option & choose the option Assign Macro.

Step 4- Next, insert the below VBA code there.

Step 5- After that, choose the preferred data range & press a button option.

Step 4- Lastly, you’ll get the wanted result.

Also Read: Unlocking the Secrets to Filter Out Excel Data to Save Time and Effort

Way 3- Change a File Format

Another option that you can try to remove leading numbers in Excel is to change a file format. Changing the file format sometimes resolves the issues related to numbers in the spreadsheet.

So, let’s do it by following the below instructions carefully.

• First, select the cells where you want to remove zeros from.
• Then, go to the Home tab and look for Number
• Under a Number section, click on the drop-down menu. This will display various options.
• There, you have to select General. This will revert your all cells to a default formatting.

Way 4- Use VALUE Function to Remove Zeros in Excel Formula

In MS Excel, the VALUE function is generally used to convert the text string that signifies a number into an actual numeric value. So, here you can use the VALUE function to delete leading zeros in Excel.

Here are the steps should be followed:

Step 1- Firstly, arrange the dataset. (As shown in the below img.) Then, insert the below formula in the C5 cell:

Step 2- After that, press Enter button to get the desired result for a single cell. But if you want to apply the formula to numerous cells then use Fill Handle.

Way 5- Use Custom Number Formatting

In order to eliminate leading zeros, you can even use the custom number formatting system. In Excel, this system allows you to modify the appearance of numbers in the cells without changing their original values.

So, let’s follow the below steps to remove the zeros:

• Initially, arrange the dataset. (As shown in the below img.).
• Then, copy the Column B by pressing Ctrl + C keys simultaneously & paste it to the Column D by pressing Ctrl + V 
• After this, choose desired column then go to Home > click on General 
• Now, you’ll get the preferred result.

Way 6- Remove Leading Zeros in Excel Using Text to Columns Feature

Another excellent feature in Microsoft Excel that can be used to remove trailing zeros is text-to-columns. It allows you to split contents of the single cell into numerous columns.

Here’s how you can use this feature to do this:

• In the beginning, arrange the dataset and select desired cell >> click on Data then Text to Columns 
• Next, in the first dialog box, press Next.
• In the second dialog box, press Next 
• After that, select the preferred column in a destination for the third dialog box & press Next 
• Now, you’ll get the preferred result.

Also Read: 3 Ways to Delete Highlighted Cells in Excel Instantly

Way 7- Try Paste Special Command

The “Paste Special” is a command in MS Excel that allows you to control over how data is displayed. Although it provides numerous options for pasting the data in different formats.

So, if you want to try Paste Special feature to remove leading zero in Excel, follow the below steps:

• Firstly, arrange the dataset, and in a C5 cell, you have to insert 1 & copy it (use Ctrl + C keys to do this).

• At this time, make right-click on a desired column & choose the option “Paste Special”.

• Then, the Paste Special popup box will appear on your screen.
• After that, choose Multiply option & click OK.

• At last, you’ll get the preferred result.

How to Repair Corrupt Excel File?

In case, while applying the above methods if an Excel document gets corrupted, use the Excel Repair & Recovery Tool. It is a professionally suggested utility that is capable of solving various errors & issues in Excel workbook. This software is easy to use & supports all the Excel versions.

Moreover, it can restore entire data such as charts, worksheet, tables, properties cell comments, etc.

Just download & install this software on your PC and repair your corrupted Excel file with ease.

Try Now       Buy Now

* Free version of the product only previews recoverable data.

Here are the steps to use this tool:

Related FAQs:

Why Do Leading Zeros Appear in Excel?

Leading zeros often appear in Excel when dealing with the codes or identification numbers.

Is There a Quick Way to Remove Leading Zeros from An Entire Column?

Yes, there is a quick way to delete leading zeroes from an entire column. You can do this by using Excel functions such as Power Query or Text to Columns.

What Is the Formula for Leading Zeros in Excel?

=TEXT(cell reference,‘#’) is the formula for leading zeroes in Excel.

Can Leading Zeros Impact Calculations?

Yes, leading zeros can result in errors during calculations.

How Do I Stop Excel from Taking Out Leading Zeros?

By using the apostrophe character (') in front of the number, you can stop Excel from taking out leading zeros.

Also Read: Excel if Blank Then Skip to Next Cell (Full Guide)

Time to Conclude

So, this is all about how to remove leading zeros in Excel 2016 or other versions. You can try any of the methods mentioned above in this post to remove leading zeros in MS Excel. All the specified ways are very effective and easy to use.

I hope you liked this post!

For additional queries, feel free to contact us on our Facebook and Twitter pages.