How to Remove Leading Zeros in Excel: 10 Effective Methods

Leading zeros in a cell value or text string in Excel help maintain data integrity in certain systems. However, those zeros also cause problems during a calculation, and that’s when an Excel user needs to remove them. To help with that task, we are going to discuss 10 useful methods in this guide on how to remove leading zeros in Excel.

Now, the process for getting rid of leading zeros is not that straightforward. It mainly depends on how those leading zeros were added to a string. It is also connected to the types or formats that values are in. As a result, there are multiple solutions for removing leading zeros.

In this guide, we discuss 10 methods that can remove all kinds of leading zeros in Excel. We will mention the types of values and situations that each method will work on during the explanations. Plus, for a better understanding, we will add pictures for every step in all the methods.

Now let’s jump in!

Different Types of Leading Zeros in Excel

When you start writing a number that begins with zero(s), you see that Excel automatically removes those zeros from the front. The reason is that if Excel detects any leading zero in values that are in the Number format, it removes those zeros by default. In general, Excel does not allow 0 (zero) in front of a numeric value.

However, if a value is formatted as text or any special style, the leading zeros stay intact. And different types of leading zeros require various kinds of methods for removing them. Take a look at the dataset below.

Different Types of Leading Zeros in Excel 1

The leading zeros in the three columns (A, C, D) are different from one another. Column A has alphanumeric values with zeros in the front. Column C has values in text format, and Column D has values in special cell formatting. Since Alphanumeric Values have both numeric and alphabetic letters in them, Excel treats them as complex text strings.

Values in Text Format are self-explanatory. The cell formatting is changed to Text. So, Excel considers values in those cells as text strings. And finally, Special Format values refer to the instance when values have to be of a certain length. Therefore, by using formulas or the Format Cells option, those values are given a certain character length which is then filled up by leading zeros in the front.

We are using the dataset below for demonstrations throughout this guide. We took an extra column and named it Operation for ease of understanding. The column is in General formatting. In most cases, the methods of removing leading zeros will be shown here.

Different Types of Leading Zeros in Excel 2

Also, in every method, we marked which types of zeros can be removed by using the respective method. For instance, if a method can be applied to Special Format values with leading zeros, we put a green checkmark under that column. This way, you will be able to differentiate which method(s) you need to use based on the type of situation you are in.

Let’s move forward then!

10 Simple Methods for Removing Leading Zeros in Excel

We mentioned earlier how removing leading zeros is not as undemanding as it may look. Nevertheless, we are going to turn this troublesome experience for an Excel user into a painless affair. The 10 methods we are going to discuss in this guide cover everything possible on how to remove leading zeros in Excel.

As the type of values in Excel determines the method you need to use, we highly suggest that you carefully read through all the methods. Every single method here is unique and works differently from the other one. Although we will attach pictures for each step, don’t forget to use the practice workbook for solid and better learning.

Now let’s begin with the first method.

Method 1: Using the Paste Special Option to Remove Leading Zeros

In the very first method of this guide on how to remove leading zeros in Excel, we are showing you how to achieve this task by using the Paste Special option. We will use the dataset below for this demonstration.

Using the Paste Special Option to Remove Leading Zeros

Only values with leading zeros that are in Text Format or Special Format can be worked on with this method, as indicated in the picture above. However, there is a slight difference when using the Paste Special option for these two formats. For Text Format, the Add option and for the Special Format, the Values option inside the Paste Special is used. We will show processes for both in this demonstration.

Let’s follow the steps!

1.1: Paste Special for Values in Text Format

In this section, we are going to show you how you can use the Paste Special option to remove leading zeros from numeric values that are in Text Format. After removing the zeros, the pasted values will maintain the source formatting. Hence, the zeros will be removed but the numbers will still be in Text Format.

To do it, you can follow the steps below.

1. Select the range of cells with values.

Paste Special for Values in Text Format 1

2. Now right-click on the first cell of the range where you want to copy it without leading zeros. Then, from the opened menu, click Paste Special.

Paste Special for Values in Text Format 2

3. A mini Paste Special options window will open up. Under Operation, select the Add option. Then click OK.

Paste Special for Values in Text Format 3

4. You will be taken back to your datasheet. You will find that the numbers don’t have leading zeros anymore.

Paste Special for Values in Text Format 4

The numbers have also been pasted in the same Text format they were originally in. You can see that the pasted numbers are left-aligned which is what Excel does with values other than Numbers. You can also check it by selecting the range and noticing it in the Number group under the Home ribbon.

1.2: Paste Special for Values in Special Format

In the case of Special Format numeric values, the use of Paste Special is slightly different. We are going to use the Values option here rather than the Add option shown in the previous section.

1. Select the range of cells with values.

Paste Special for Values in Special Format 1

2. Now right-click on the first cell of the destination column where you want to post the values without leading zeros. Then click the Paste Special option (as we showed in Method 1.1 Step 2). Then, in the Paste Special window, select Values under the Paste option. Finally, click the OK button.

Paste Special for Values in Special Format 2

3. You should see that your copied numbers don’t have any leading zeros!

Paste Special for Values in Special Format 3

You will also see that the pasted values are aligned right. It means that they have maintained the source formatting of the Special Number Format but do not have those leading zeros anymore.

Pretty neat, right?

Method 2: Removing Leading Zeros by Multiplying or Dividing Values

Now we will show how you can remove leading zeros by simply multiplying or dividing those values. This method uses a single arithmetic formula for removing leading zeros from your values. This technique is useful for values in both Text Format and Special Format.

Removing Leading Zeros by Multiplying or Dividing Values

Let’s see how to do it!

2.1: Multiplying or Dividing Values in Text Format

As we mentioned, a single arithmetic operator is used in this method. You can multiply with 1 or divide with 1. It is up to you.

1. Take a new empty column. Now, click on the first cell and simply write the arithmetic formula for multiplication or division.

Begin by writing = and then click on the first cell with the value in Text Format. Then put * or / based on which arithmetic operator you want to use. Then put 1 as the multiplier or divisor, and hit Enter.

Multiplying or Dividing Values in Text Format 1

In this demonstration, we clicked on a cell and then started writing the formula with =. Then clicked on the first cell with the value in Text Format (cell C3) and then put * as the arithmetic operator for multiplication. Finally, we entered 1 as the multiplier and then pressed the Enter button.

2. You will see that the cell where you wrote the formula is now showing the value without any leading zeros.

Multiplying or Dividing Values in Text Format 2

3. Now use the Fill Handle to fill up the column with the formula for all the values from your source column.

Multiplying or Dividing Values in Text Format 3

This method maintains the source formatting for values. You can see that the values are in left alignment, as all the texts are in Excel by default. You can also select any or all the cells in the destination column and see the current format of those values inside the Number group under the Home ribbon.

2.2: Multiplying or Dividing Values in Special Format

For values consisting of leading zeros in a Special Format, the process is the same. You can use multiplication by 1 or division by 1 to remove leading zeros from your values. However, for a change, since we used multiplication in the previous section, we are going to use division in this one.

1. Take a new empty column and click on the first cell. Then write the easy arithmetic formula for multiplication or division. Write = and then click on the first value with leading zeros, put the arithmetic operator for multiplication or division, and then write 1. The explanation is given in detail in Method 2.1 Step 1.

In this demonstration, we did it with the Special Format values and chose to do a division operation for a change.

Multiplying or Dividing Values in Special Format 1

2. You should see that in the destination column, the leading zeros have been removed from your original data.

Multiplying or Dividing Values in Special Format 2

3. Now fill the column with the formula by using the Fill Handle. You should see here too that the values without leading zeros have followed the source formatting here as well (right-aligned both in the source and destination). Therefore, these are still Special Number Format values but without leading zeros in front.

Multiplying or Dividing Values in Special Format 3

Easy and simple!

Method 3: Using the Text to Columns Option

The Text to Columns option in Excel is a wonderful option with so many applications. It shows its magic in removing leading zeros from values as well. When you have a column with hundreds or thousands of values with leading zeros that you need to remove, this option is always there to save your day.

This method converts values to Numbers (or General) irrespective of whatever format the source data have them in. After removing leading zeros, you can go ahead and do any kind of calculations with them. However, by using this option, you can remove leading zeros from values only in Text Format and values in Special Number Format, as the picture below will tell you.

Using the Text to Columns Option

Just like we have done so far, we are discussing the processes for removing those leading zeros from Text Format values and Special Format values separately.

3.1: Removing Leading Zeros with Text to Columns for Values in Text Format

For values that are in Text Format, the Text to Columns option gives you one of the best ways to get rid of those leading zeros from the front. Let’s see how to do it through steps and corresponding pictures.

1. First of all, select the range of cells from where you want to remove leading zeros. Then go to the Data ribbon by clicking on the Data tab. Next, click the Text to Columns button in the Data Tools group.

Removing Leading Zeros with Text to Columns for Values in Text Format 1

2. You will see a wizard window titled Convert Text to Columns Wizard. Select Delimited under the Original data type, and then click Next.

Removing Leading Zeros with Text to Columns for Values in Text Format 2

3. Make sure that nothing is checked under Delimiters. Then click the Next button again.

Removing Leading Zeros with Text to Columns for Values in Text Format 3

4. Here, you need to do two things. First, select General under the Column data format. Then click on the box beside Destination to select a new destination for your data. By default, it has an Absolute Reference to the first cell of your initially selected range.

Removing Leading Zeros with Text to Columns for Values in Text Format 4

If you don’t change it, the source data with leading zeros will be replaced by data with removed leading zeros. This is a destructive process. We suggest you select a new destination for your Text to Columns data so that the original data also survives.

5. After clicking on the Destination box, now simply select the range of cells where you want to put the values without leading zeros. Then click Finish.

Removing Leading Zeros with Text to Columns for Values in Text Format 5

6. Your destination column should now have the values without leading zeros.

Removing Leading Zeros with Text to Columns for Values in Text Format 6

You can also notice that although the source values were in Text Format, the values in the destination column are now in Number Format. The values in this destination column are right-aligned which is one major indication that the values are recognized as Numbers in Excel.

3.2: Removing Leading Zeros with Text to Columns for Values in Special Format

The procedure for removing leading zeros with the Text to Columns option in the case of the Special Number Format is quite similar.

1. First of all, select your range of cells with Special Format values containing leading zeros. Then go to the Data ribbon and click Text to Columns in the Data Tools group.

Removing Leading Zeros with Text to Columns for Values in Special Format 1

2. Make sure that the Delimited option is selected and then click Next.

Removing Leading Zeros with Text to Columns for Values in Special Format 2

3. No option under Delimiters should be checked. After ensuring it, click Next again.

Removing Leading Zeros with Text to Columns for Values in Special Format 3

4. Select General under the Column data format and then click on the Destination box.

Removing Leading Zeros with Text to Columns for Values in Special Format 4

5. Afterward, select the range of cells in an empty column as the destination of your values. Finally, click the Finish button.

Removing Leading Zeros with Text to Columns for Values in Special Format 5

6. As you can see, the leading zeros are now gone from values in your destination column.

Removing Leading Zeros with Text to Columns for Values in Special Format 6

Like Method 3.1, the Text to Columns option here has also converted the Special Format values to Number format. The right alignments of the values attest to that. You may also take a look in the Number group under the Home ribbon. You will find that the values are in General format.

Removing Leading Zeros with Text to Columns for Values in Special Format 7

A no-sweat method for sure!

Method 4: Changing Cell Formatting to Remove Zeros from the Front

This method deals with a special case when you know for certain that the leading zeroes are a result of Special Formatting of the values for a fixed number length. Let us explain.

Special formatting is used when a specific length of character has to be maintained in a cell. For example, if you have some three digits values and some two digits values in your dataset, they may look untidy and cause further complications in some stages of your data handling.

To solve this problem, the most popular way is to format the values to have a certain length. So, as there are three and two digits in the example, why not format it to have five digits in total? The values will stay the same but to follow the five-character length requirement, Excel will add zeros in the front to adjust it and make all those values have a five-character length.

When you know for sure that the leading zeros in your dataset are of this kind, you can follow an easy technique to remove them. Follow the steps we are demonstrating below.

1. Select the range of cells first. Then right-click on the selection and then select the Format Cells option from the context menu. Alternatively, you may press the Ctrl+1 buttons to bring up the same Format Cells window.

Changing Cell Formatting to Remove Zeros from the Front 1

2. In the Format Cells window, click on the Number tab first. Then click on Custom under the Category list. Now look for the Type box as marked (3) in the picture below. It should have multiple zeros there indicating the custom fixed character length.

Changing Cell Formatting to Remove Zeros from the Front 2

3. Remove all the zeros from that box and then click OK.

Changing Cell Formatting to Remove Zeros from the Front 3

4. You should see that the leading zeros have been removed from your selected range of cells with values that initially had zeros in front of them.

Changing Cell Formatting to Remove Zeros from the Front 4

For this type of leading zeros, there isn’t any faster method available out there!

Method 5: Converting Text to Number with Error Checking

This is a special method for removing leading zeros from values that are in Text Format. As one of the most powerful options in Excel, the Error Checking option is used to convert those values with leading zeros in text format to a number format. This will remove zeros from the front because, as we said earlier, Excel does not keep leading zeros when values are in Number format.

Now, there are a couple of prerequisites if this is the method you want to use. First of all, The Error Checking option has to stay enabled. To check the current status of it, go to File and then click Options to open the Excel Options window. Then click on Formulas and find the Error Checking section among the options. Make sure that Enable background error checking is ticked, and finally, click the OK button.

Converting Text to Number with Error Checking 1

We are going to use the dataset below to explain the steps on how to remove leading zeros in Excel by using the Error Checking option.

Converting Text to Number with Error Checking 2

The second and final thing that an Excel user needs to make sure of is that the values are indeed in Text Format and the Error Checking option is working. Notice the marked areas in the picture below. There are little green triangles in the top-left corner of the cells. It means that the Error Checking option has detected an error in the cell. And when you click on a cell, a little button with the warning icon will be visible in this case.

Converting Text to Number with Error Checking 3

This is how you can determine that these values are in Text Format and the Error Checking option will convert these values to Number format. As a result, it will remove leading zeros from the values.

Now let’s go through the steps for this method.

1. Select the range of cells with values in Text Format. You may determine the format by looking at those little triangles at the top-left corners of each cell.

After the selection, you will get a little button with a warning symbol close to your selected range.

Converting Text to Number with Error Checking 4

2. Click on that little button with the warning sign. Then click Convert to Number from the newly-opened up menu.

Converting Text to Number with Error Checking 5

3. You will see that the leading zeros have been removed.

Converting Text to Number with Error Checking 6

Isn’t this method much easier than it appears to be? It is also super fast. Just follow the prerequisites and you will cut down your work with much ease!

Method 6: Using the VALUE Function

The VALUE Function converts numeric values in different formats to number formats. It is extremely useful if all you have is purely numeric values with leading zeros. Unfortunately, this function does not work with Alphanumeric Values. It will show a #VALUE error in this case.

We are going to use the dataset shown below. It already shows that the VALUE function will work only on numeric values with leading zeros in Text Format and Special Format.

Using the VALUE Function 1

The formula for the VALUE function is pretty straightforward. It is =VALUE(text). This formula converts values included or referred to in the text argument of the formula to Numbers. And as we have said a few times so far, the number format in Excel does not keep zeros in the front. This is how the VALUE function will remove leading zeros in this method.

Let’s see how to do it.

1. Take a new empty column. Click on the first cell and then write down the formula, =VALUE(text).

Using the VALUE Function 2

2. In the text argument, Cell Reference to the value you want to remove the leading zeros from. Then hit Enter.

In the demonstration below, we selected cell C3 as the Cell Reference in the text argument of the formula.

Using the VALUE Function 3

3. Now you will see that your value with leading zeros in the Text Format has been converted to the number format and has no zeros in front of it. Also, the output is right-aligned which indicates this conversion.

Using the VALUE Function 4

4. Now drag down the Fill Handle to fill up the column with the formula which will convert all the values in Text Format to the Number format. During this process, it will remove all the leading zeros from those values.

Using the VALUE Function 5

5. Follow the same procedure for Special Number Format values, as we did in this demonstration.

Using the VALUE Function 6

6. After pressing the Enter button, use the Fill Handle to fill up the column with the formula. Just like before, it will remove all the leading zeros from your values in Special Format.

Using the VALUE Function 7

So effortless!

Method 7: Nested VALUE & TEXT Functions

Using the VALUE function to remove leading zeros is certainly one of the best ways. It removes those zeros by converting values to numbers. But, in specific situations, you may need to protect your values from getting involved in some calculations. And as any Excel user knows, keeping a numeric value in text format allows a numeric value to be safe from getting included in an Excel calculation.

But the leading zeros still need to go from your values. What to do then? This method will show how you can use a nested formula to remove zeros from the front of a value. But it will also ensure that the output format is set as text. You don’t need to go through the extra steps of converting the output to the text format in the end separately.

Nested VALUE _ TEXT Functions 1

We will use the dataset above to achieve the ultimate result. And as you can see, this method works only with values in Text Format and Special Format. The formula for this method is =TEXT(VALUE(text),format_text). Here, in the text argument, we will use Cell Referencing to use the source data. And the format_text will be “#”. It will refer to the existing number format for the values.

Now let’s see how we can do it.

1. Take a new empty column and select the first cell. Then write the formula, =TEXT(VALUE(text),format_text). For the text argument in the formula, select the first cell of your data column that has leading zeros. And in format_text, put “#”. Make sure that you are writing the hashtag within closed quotations. When you finish writing the formula, press the Enter button.

In this demonstration, we referred to cell C3 for the text argument within the formula.

Nested VALUE _ TEXT Functions 2

2. After pressing Enter, you will see that the leading zeros have been removed from your selected cell’s value. Also, the result is automatically left-aligned which means that it is in text format. This is a result of the application of nested TEXT and VALUE functions.

Nested VALUE _ TEXT Functions 3

For contrast, see the picture below where only the VALUE function is used. The result is a numeric value that has the right alignment, by default.

Nested VALUE _ TEXT Functions 4

3. Fill up the column with the formula by using Fill Handle and remove all the leading zeros from your source values.

Nested VALUE _ TEXT Functions 5

4. Follow the same process for removing zeros from the front in case of Special Number Format.

Nested VALUE _ TEXT Functions 6

5. Seamlessly, you now have all the leading zeros removed from your data. And the result is in text format, as per the requirement.

Nested VALUE _ TEXT Functions 7

That’s it.

Method 8: Using a Nested VALUE, TRIM, and CLEAN Functions

There are times when you copy-paste data from a webpage or some non-Excel document. Almost 7 out of 10 times, those data have nonprintable characters or unnecessary leading spaces in front of them. If you try to use a formula with those data without cleaning them up, the formula will never work.

This complication is very much present in an operation of removing leading zeros from values. Those nonprintable characters or unwanted spaces in the front interfere with the VALUE function. Let’s take a look at the picture below.

Using a Nested VALUE, TRIM, and CLEAN Functions 1

Here, only the VALUE function is used. But as you can see, the result is showing an error. It’s because there are nonprintable characters like a tab character in the front of those values. You cannot see or detect those in the source data. But when you try a formula on them, you get errors constantly. For this reason, we bring to you an all-in-one solution. It will clean up your data first and then remove leading zeros from them, all in one go!

We will use a nested formula with three different functions here; VALUE, TRIM, and CLEAN. The CLEAN function will remove the nonprintable characters (like a tab character or line breaks) from the source data with leading zeros. The TRIM function will remove the unnecessary spaces, and the VALUE function will then do the job of converting values to number format and thus, removing the leading zeros from those values.

See the image below.

Using a Nested VALUE, TRIM, and CLEAN Functions 2

As you can see, the same source values are now showing results perfectly with the use of a nested VALUE, TRIM, and CLEAN formula. But it most definitely gave out errors when only the VALUE function was used.

This nested formula can be used to remove leading zeros from both values in Text Format and Special Number Format. And to maintain consistency throughout the guide, we will use the same dataset shown below in this demonstration.

Using a Nested VALUE, TRIM, and CLEAN Functions 3

Let’s go through the steps then!

1. Write down the formula for this method. It is =VALUE(TRIM(CLEAN(text). Here, for the text argument, use the Cell Reference technique. Click the cell that has your value with leading zeros.

Using a Nested VALUE, TRIM, and CLEAN Functions 4

2. After selecting the cell with the value, close the brackets and then hit Enter.

Using a Nested VALUE, TRIM, and CLEAN Functions 5

3. You should see that the result is properly showing your value excluding the leading zeros without any error.  And as always, the result is in a Number format because the VALUE function converts text strings with numeric values to simple numbers.

Using a Nested VALUE, TRIM, and CLEAN Functions 6

4. Now click and drag the Fill Handle to fill up the column with the formula. This will remove all the leading zeros from your source column and give you the ultimate result.

Using a Nested VALUE, TRIM, and CLEAN Functions 7

5. Follow the same process for other columns. Use the nested formulas =VALUE(TRIM(CLEAN(text) to achieve the intended result.

Using a Nested VALUE, TRIM, and CLEAN Functions 8

6. Use the Fill Handle to apply the formula for all the source data. Afterward, you should see that your destination column has all those data without any zeros in front of them.

Using a Nested VALUE, TRIM, and CLEAN Functions 9

This method also provides a significantly important answer to the situation when you try to use the VALUE function to convert apparent numeric text strings but fail repeatedly due to a #VALUE error. Be mindful of the nonprinting characters and use this nested VALUE, TRIM, and CLEAN formula for a foolproof result in terms of removing leading zeros in Excel.

Method 9: The NUMBERVALUE Function

In the previous method, we have shown you how to remove leading zeros with a nested formula when values have nonprintable characters and spaces. That is only applicable when those characters are included in the front of those values. The VALUE function alone cannot remove those zeros for it, so TRIM and CLEAN functions are also needed.

But if your values with leading zeros have spaces between characters, neither the VALUE function nor even the nested VALUE, TRIM, and CLEAN formulas can convert those text strings to numbers and remove zeros from the front. This is a huge problem that is solved by the NUMBERVALUE function.

Take a look at the image below. When source data has spaces between characters, the VALUE function returns a #VALUE error. Since the goal is to convert those numeric text strings to the Number format and thus remove leading zeros, those errors cause nothing but huge frustrations.

The NUMBERVALUE Function 1

The NUMBERVALUE function comes into play here. When you use it, values in Text Format get converted even if they have spaces between characters. As a result, it removes those zeros from the front. See the picture below to understand the difference.

The NUMBERVALUE Function 2

Now, this NUMBERVALUE function can be used in many cases. However, it does not work on Alphanumeric Values since this function works on purely numeric values in different formats. It is indicated in the image shown here.

The NUMBERVALUE Function 3

By using this dataset, now we are going to show you a special scenario of how to remove leading zeros in Excel by using the NUMBERVALUE function.

1. Take a new column and select the first cell in it. Then start writing the formula for the NUMBERVALUE function, =NUMBERVALUE(text). There are more optional arguments in this formula but we will use only the mandatory argument in it; the text argument.

The NUMBERVALUE Function 4

2. After writing “=NUMBERVALUE(”, click on the first cell in your source data column for Cell Referencing. Then close the bracket and press Enter.

The NUMBERVALUE Function 5

3. You should see that in your destination cell, the source value is visible without leading zeros and all the in-between spaces.

The NUMBERVALUE Function 6

4. Now fill the column with the formula by using the Fill Handle. This will give you values in Number format without leading zeros and spaces.

The NUMBERVALUE Function 7

5. Follow the same procedures for other columns that have data with leading zeros.

The NUMBERVALUE Function 8

This NUMBERVALUE function is a more powerful version of the combined VALUE, TRIM, and CLEAN formulas discussed in the previous method. The NUMBERVALUE does everything that the VALUE, TRIM, CLEAN nested formula does. On top of that, it removes spaces between characters which is not possible by using the nested formula.

If your Excel version is not older than Excel 2013, you should use this method as one of your primary options.

Method 10: Removing Leading Zeros from Alphanumeric Values

This is the most complex method in this guide. But, fear not. We are here to explain each step in the easiest way possible. Despite being a complicated one, this method is also the only one in this guide that will serve you every single time when you want to remove leading zeros. It works with any kind of formatting the values are in!

The idea is simple. We will use a formula that can determine the position of the first non-zero character after leading zeros in a value. The formula will then count all the characters from that first non-zero character position. In this way, simply one formula will work on any kind of string or numbers that have zeros in front.

But first, let’s break down the main formula that we are going to use in this method, the RIGHT formula. It is =RIGHT(text,[number_of_characters]). Here, the text argument refers to the input value or Cell Reference, and the number_of_characters determines how many characters from the right direction will be extracted from the input value or string of text.

As the number_of_characters will vary because not all the strings or values have the same number of zeros in the front, we will use some more formulas to make the value of number_of_characters dynamic. We will discuss relevant information and break down all those formulas in respective steps. Also, as you can see, we will use the dataset shown below, and the leading zeros will be removed from all these types of formatting.

Removing Leading Zeros from Alphanumeric Values 1

Let’s see in some steps how to use this complex method in a simple way to achieve a mind-blowing result!

1. [FORMULA EXPLANATION] Take a new column as the destination for your result and then click on the first cell. Now start writing down the formula, =RIGHT(cellReference,LEN(cellReference). Click on the first cell of your source column in place of cellReference.

We are going to use the LEN function with the help of SEARCH, LEFT, and SUBSTITUTE functions to determine the position and length of characters without leading zeros.

Removing Leading Zeros from Alphanumeric Values 2

2. [FORMULA EXPLANATION] Now let’s see the whole formula for this demonstration and discuss every bit of it. This way, it will be far easier to understand the intention behind using this approach.

The source data is in cell A3, and the formula we wrote for this operation is=RIGHT(A3,LEN(A3)-SEARCH(LEFT(SUBSTITUTE(A3,”0″,””),1),A3)+1). If you simply want to copy-paste the formula without wanting to know how it works, you can copy the formula from here and then replace those Cell References with the one where your data is. After putting the formula, be sure to press Enter.

But if you would like to understand the process by reading the explanation, it is given right below the picture here.

Removing Leading Zeros from Alphanumeric Values 3

Here, the role of whole LEN(A3)-SEARCH(LEFT(SUBSTITUTE(A3,”0″,””),1),A3)+1 is to determine the character length without leading zeros. “LEN(A3)” is the entire character length of the value in the source cell and it includes leading zeros. Then “-SEARCH(LEFT(SUBSTITUTE(A3,”0″,””),1),A3)+1” calculates and subtracts the leading zeros from “LEN(A3)”.

Here, if we begin at the center, the SUBSTITUTE function finds all the zeros till the first non-zero character from the left (it is nested within the LEFT formula) and replaces all those leading zeros with nothing (which means that the zeros are now deleted). Now, these are the characters without leading zeros for the LEFT formula’s text argument. Then as the number_of_characters, “1” is included. This is the “LEFT(SUBSTITUTE(A3,”0″,””),1)” part of the formula.

Finally, the SEARCH formula in “SEARCH(LEFT(SUBSTITUTE(A3,”0″,””),1),A3)+1” finds the position of the first non-zero character from the string of text. The +1 in the last part makes sure that this first position character itself is also included in the calculation of non-leading zero character length. Otherwise, the calculation would consider counting from that character instead of including it in the calculation.

This whole thing is now subtracted from LEN(A3) which, as a part of the RIGHT formula, tells Excel the length of the characters without leading zeros in the source string (in this case, cell A3). As a result, you get your Alphanumeric Values or text strings without leading zeros!

3. As you hit Enter, you get your result without leading zeros!

Removing Leading Zeros from Alphanumeric Values 4

4. Now fill up the column with the formula to get results from all the Alphanumeric Values in the source column.

Removing Leading Zeros from Alphanumeric Values 5

5. You can use the same formula to remove leading zeros from numeric values in Text Format.

Removing Leading Zeros from Alphanumeric Values 6

Pretty effortlessly, you will get the same result.

Removing Leading Zeros from Alphanumeric Values 7

6. Do the same for values in Special Number Format to remove leading zeros.

Removing Leading Zeros from Alphanumeric Values 8

You will see that leading zeros have been removed in this case as well.

Removing Leading Zeros from Alphanumeric Values 9

This method can be considered the mother of all methods for removing leading zeros in Excel due to its flexibility.

Ending Thoughts

In this guide, we showed 10 different methods for how to remove leading zeros in Excel. If we consider difficulty levels, this guide covered everything. Depending on the types and how much data you are dealing with, you can choose your preferred method from the guide.

As we discussed, leading zeros can always be removed from a string of text or any other special formatted value by converting it to a number. In this case, Excel removes leading zeros by itself after the conversion. Nevertheless, there are moments when it is impossible to do, like in the case of alphanumeric values.

Considering these things, Method 10 is the best technique to go with for removing leading zeros. It is one method that does not remove those zeros by converting strings to numbers. Rather, it finds the location where leading zeros end in a value and then gets rid of those zeros irrespective of the source formatting.

But if you only have to deal with values in text format and special format, Method 3 and Method 9 will serve you well. We strongly suggest you learn all the methods that we discussed in this guide. Use our practice workbook for a closer look. We can guarantee that learning all these methods will help you in the long run and turn you into a more competent and smarter Excel user.

Leave a Comment