How to Remove Leading Zeros in Excel (5 Easy Ways) - Trump Excel (2023)

Many people have a love-hate relationship with leading zeros in Excel.

Sometimes you will and sometimes you won't.

Although Excel has been programmed to automatically remove leading zeros from numbers, there are a few cases where it does.

In this Excel tutorial I will show youhow to remove leading zerosin your figures in Excel.

So let's get started!

Possible reasons why you might have leading zeros in Excel

As I mentioned, Excel automatically removes leading zeros from numbers. For example, if you enter 00100 in an Excel cell, it will automatically convert to 100.

In most cases, this makes sense since these leading zeros aren't really significant.

But in some cases, you may want to.

Here are some possible reasons why your numbers may have leading zeros:

• If the number has been formatted as text (especially by adding an apostrophe before the number), keep the leading zeros.
• The cell can be formatted so that it always displays a certain length of a number. And if the number is less, leading zeros are added to compensate. For example, you can format a cell to always display 5 digits (and if the number is less than five digits, the leading zeros are automatically added)

The method we choose to remove leading zeros will depend on what is causing it.

Also read:How to Add Leading Zeros in Excel

Therefore, the first step is to identify the cause so that we can choose the correct method to remove these leading zeros.

How to remove leading zeros from numbers

There are several ways to remove leading zeros from numbers.

In this section, I'll show you five of these methods.

Convert text to numbers using error checking

If the reason for the leading numbers is because someone added an apostrophe before those numbers (to convert them to text), you can use the error checking method to convert them back to numbers with one click.

This is probably the easiest method to get rid of leading zeroes.

Here I have a data set where I have the numbers that have an apostrophe in front of them, as well as the leading zeros. This is also why you see these numbers left justified (whereas by default the numbers are right justified) and they also have leading zeros.

Below are the steps to remove these leading zeros from these numbers:

1. Select the numbers from which you want to remove leading zeros. You will notice that there is a yellow icon in the upper right corner of the selection.
2. Click the yellow bug check icon
3. Click 'Convert to Number'

And so! The above steps would beremove the apostropheand convert those text values ​​back to numbers.

And since Excel is programmed to remove leading spaces from any number by default, you'll find that this automatically removes all leading zeroes.

Note: This method would not work if leading zeros were added as part of the cells' custom number format. To handle these cases, use the methods described below.

Change the custom number format of cells

Another very common reason why your numbers may appear with leading zeros is when your cells have been formatted to always display a certain number of digits in each number.

Many people want numbers to appear consistent and have the same length, so they specify the minimum length of the numbers by changing the format of the cells.

If eg. If you want all numbers to appear as 5-digit numbers, if you have a number with only three digits, Excel automatically adds two leading zeros.

Below I have a dataset where a custom number format has been applied to always display a minimum of five digits in the cell.

And the way to get rid of these leading zeros is to simply remove the existing formatting from the cells.

Here are the steps to do this:

1. Select cells that have numbers with leading zeros
2. Click the Home tab
3. No group numbers, click no menu hanger Number Format
4. Select 'General'

The steps above would change the custom number formatting of the cells and now the numbers would display as expected (where there would be no leading zeros).

Note that this technique will only work when the reason for the leading zeros is a custom number format. It would not work if an apostrophe was used.convert numbers to text(in that case, use the above method)

Multiply by 1 (using the Paste Special technique)

This technique works in both scenarios (where the numbers have been converted to text using an apostrophe or where a custom number format has been applied to the cells).

Suppose you have a data set like the one below, and you want to remove leading zeros from it.

Below are the steps to do this.

1. Copy any empty cells in the worksheet
2. Select the cells where you have the numbers you want to remove leading zeros from
3. Right-click the selection and click Paste Special. This will open the Insert Special dialog.
4. Click on the 'Add' option (in the operations group)
5. Click OK

The above step adds 0 to the selected cell range and also removes all leading zeros and apostrophes.

While this doesn't change the cell's value, it converts all text values ​​to numbers and also copies the formatting of the blank cell you copied (thus replacing the existing formatting that caused the leading zeros to appear).

This method only affects numbers. If you have a text string in a cell, it remains unchanged.

Using the VALUE function

Another quick and easy method to remove leading zeros is to use the value function.

This function takes one argument (which can be the text or the cell reference containing the text) and returns the numeric value.

This would also work in any scenario where the leading numbers are the result of an apostrophe (used to convert numbers to text) or a custom number format.

Suppose I have a dataset as shown below:

Below is the formula that would remove leading zeros:

=VALUE(A1)

Note: If you still see the leading zeros, go to the Home tab and change the cell format to General (in the Number Format dropdown menu).

Use text in columns

Although the Text to Columns function is used tosplit a cell into multiple columns, you can also use it to remove leading zeros.

Suppose you have a data set as shown below:

Here are the steps to remove leading zeros using text for columns:

1. Select the range of cells that contains the numbers
2. Click on the 'Data' tab
3. Click "Text to Columns" in the Data Tools group.
4. In the 'Convert Text to Columns' wizard, make the following changes:
   1. Step 1 of 3 – Select 'Delimited' and click Next
   2. Step 2 of 3 – Uncheck all delimiters and click Next
   3. Step 3 of 3 – Select a destination cell (B2 in this case) and click Done

The above steps should remove all leading zeros and just give the numbers. If you still see leading zeros, change the cell format to General (this can be done from the Home tab)

How to remove leading zeros from text

While all of the above methods work fine, they only target cells that have a numeric value.

But what if you have alphanumeric or text values ​​that also contain some leading zeros.

The above methods would not work in that case, but thanks to the amazing formulas in Excel, you can still get that time.

Suppose you have a data set like the following, and you want to remove all leading zeros:

Below is the formula to do this:

=DIREITA(A2,LÆN(A2)-INDSTILLINGER(ESQUERDA(SUBSTITUIR(A2,"0",""),1),A2)+1)

Let me explain how this formula works.

ohCOMPENSATIONpart of the formula replaces the zero with a space. So, for the value 001AN76, the substitution formula gives the result 1AN76

ohLEFT formulathen extracts the leftmost character from the resulting string, which would be 1 in this case.

ohFIND formulathen finds the leftmost character given by the LEFT formula and returns its position. In our example, for the value 001AN76, it would return 3 (which is position 1 in the original text string).

1 is added to the result of the FIND formula to ensure that we extract the entire text string (except leading zeros)

Then the result of the FIND formula is subtracted from the result.Fórmula LEN(used to specify the length of the entire text string). This gives us the length of the text ring minus any leading zeros.

This value is then used withfunction REALto extract the entire text string (except leading zeros).

If there's a chance for itleading or trailing spacesin your cells, it is better to useTRIM functionwith each cell reference.

So the new formula with the TRIM function added would appear as follows:

= HØJRE(TRIM(A2), LEN(TRIM(A2)) - FIND(VENSTRE(REPLACE(TRIM(A2), "0", ""), 1), TRIM(A2)) + 1)

Here are some easy ways you can use to remove leading zeros from your data set in Excel.

I hope this tutorial has been useful to you!

Other Excel Tutorials You May Like:

• Hide null values ​​in Excel | Leave cells blank if the value is 0
• How to replace empty cells with zeros in Excel PivotTables
• Delete rows based on a cell value (or condition) in Excel
• How to prevent Excel from converting numbers to dates
• How to Convert Serial Numbers to Dates in Excel