How to remove leading zeros in Excel (8 easy methods) (2023)

This tutorial will demonstrate how to remove leading zeros in Excel. Leading zeros are zeros that are placed at the beginning of the number but have no value. When dealing with a large amount of data, sometimes unnecessary zeros appear unexpectedly. This creates a lot of data storage and confusion. These leading zeros also have no additional meaning. So we want to learn how to remove these leading zeros in Excel.

Download the practical notebook

You can download the practical notebook here.

8 Easy Methods to Remove Leading Zeros in Excel

We will try to remove leading zeros in Excel in 8 different and proper ways. If you follow the steps correctly, you will learn how to remove leading zeros in Excel on your own. The steps are:

1. Convert text to numbers

In this case, our goal is to remove the leading zeros from the Excel file by converting the text to a number option. Sometimes the numbers in your spreadsheet don't work as numbers; they do not perform any type of arithmetic operations as they should and may even produce errors. The reason for this is that although they look like numbers, they are actually formatted as text. we will use thisConvert text to numbersopportunity to meet our goals. We can achieve our goal by following the steps below.

triune:

• First, arrange a data set like in the image below.

• then selectconvert to numbersPossibility of error mark.

• In the end, you will get the desired result.

See more information: How to Adjust Cell Format Number with Text in Excel (4 Ways)

2. Use custom number format

At this point, our goal will be to remove the leading zeros using the methodcustom number formatsystem. We can achieve our goal by following the steps below.

triune:

• To get started, organize a data set like the image below.

• Also copy the full .Coluna BuserCtrl+Cbuttons and insert itKolonne D.userCtrl+Vpossibility.

• Also go choosedesired column > Home > Generalopportunities.

• In the end, you will get the desired result.

See more information: How to Format Numbers with VBA in Excel (3 Methods)

3. Using the VALUE function

Now we will remove the leading zeros usingthe VALUE function. ohCOURAGEThe function is classified intoTEXT functionsin excel. Converts a given text string that represents a number to a numeric value. We can achieve our goal by following the steps below.

triune:

• First, arrange a data set like in the image below andC5cell enter the following formula.

• Second, if you pressWritingbutton, you will get the result of the cell and then usefilling handleto apply the formula to all desired cells.

• In the end, you will get the desired result.

See more information:[Solved!] Excel Doesn't Recognize Numbers in Cells (3 Techniques)

4. Combination of VALUE and TEXT functions

At this point we will try to remove the leading zeros using a combination ofCOURAGEmiTEXTFunctions in Excel. keep oradd leading zerosto a numeric value, theTEXTThe function can serve the purpose of the best fit formula. In the following data set, assuming we want to resize all numbers to display a similar size foradding leading zerosbefore the numbers. We can achieve our goal by following the steps below.

triune:

• To get started, arrange a data set like in the image below andC5cell enter the following formula.

• If you also pressWritingbutton, you will get the result of the cell and then usefilling handleto apply the formula to all desired cells.

• In the end, you will get the desired result.

RRead more: How to continue with leading zeros in Excel (10 useful methods)

5. Multiply the column by 1

Next, we intend to remove leading zeros by multiplying by 1. Some simple and complicated math can be used to correctconvert to numbersmistake. The trick is... simplymultiplyoto sharevalues ​​know1. You can also add zero to the values ​​and you will get the same result. Here we will also use the help column. We can achieve our goal by following the steps below.

triune:

• First, arrange a data set like the image below.

• Second, enter1in all cells ofcolumna C.

• thirdly iD5cell enter the following formula.

• Fourth, if you pressWritingbutton, you will get the result of the cell and then usefilling handleto apply the formula to all desired cells.

• In the end, you will get the desired result.

See more information: How to round numbers to the nearest multiple of 5 in Excel

similar readings

• How to Change Number Format from Comma to Period in Excel (5 Ways)
• Put 0 in Front of Numbers in Excel (5 Useful Methods)
• How to Format Numbers to Millions in Excel (6 Ways)
• Type 00 in Excel (11 easy ways)
• How to Convert Numbers to Percentages in Excel (3 Quick Ways)

6. Use the special insert command

In this case, our goal is to remove the leading zeros from the Excel file using the commandinsert special command. Paste Special is a feature that gives you more control over how content is displayed. For example, text copied from a web page often preserves most or all of the content.HTMLformatting. Wearingcollar especial, you can choose to insert plain text.Con collar especial, you can copy not only the data but also the width of the source column to the destination columns. You can even use Paste Special to perform calculations like addition, subtraction, multiplication, and division. They are another powerful way to use Excel.insert special command. We can achieve our goal by following the steps below.

triune:

• First, arrange a data set like in the image below and IC5cell insert1and copy it usingCtrl+Cbuttons.

• Then right click on the desired column and selectcollar especialpossibility.

• From here on it iscollar especialA dialog box opens on the screen.
• then selectMultiplyoption and clickBueno.

• In the end, you will get the desired result.

See more information:Excel number format not working (2 reasons with solutions)

7. Using the Text to Columns feature

Now we will remove the leading zeros using the Text to Columns function in Excel. We can achieve our goal by following the steps below.

triune:

• First, arrange a data set like the image below and selectthe desired cell > Data > Text to Columnsopportunities.

• Second, press the button in the first section of the dialog.Nextpossibility.

• Third, again in the second section of the dialog, press the buttonNextpossibility.

• Fourth, select the desired column in the destination from the third section of the dialog box and press the buttonNextpossibility.

• In the end, you will get the desired result.

See more information:How to concatenate numbers with leading zeros in Excel (6 methods)

8. Use of VBA code

At this point we will try to remove the leading zeros usingVbacode. Visual Basic for Applications(VBA) the macro language is a key component. Excel users useVbaMacros to customize and automate Excel. We can achieve our goal by following the steps below.

triune:

• To get started, go toDeveloper > Insert > Form Controlsopportunities.

• You will also get the result below.

• Also right click on the button and selectAssign macropossibility.

• Then paste the VBA code here.

Sub Removing_Leading_Zero()'Código por ExcelDemy.comDim Remove_Range As RangeDim Wrk_Rng As RangeOn Error Resume NextxTitleId = "ExcelDemy Excluir zeros à esquerda"Set Wrk_Rng = Application.SelectionSet Wrk_Rng,WRng_Address = Application.SelectionSet Wrk_Rng,WRng. , Tipo : =8)Wrk_Rng.NumberFormat = "Generelt"Wrk_Rng.Value = Wrk_Rng.ValueEnd Sub

• Then select the desired data range and press the Option button.

• In the end, you will get the desired result.

See more information: How to Use Number Format Code in Excel (13 Ways)

How to remove leading zeros from non-numeric values ​​in Excel

In this case, our goal is to remove leading zeros from non-numeric values. Non-numeric means it will contain some text or something other than a number. We can achieve our goal by following the steps below.

triune:

• First, arrange a data set like in the image below.

• Second, inC5cell enter the following formula.

• Third, if you pressWritingbutton, you will get the result of the cell and then usefilling handleto apply the formula to all desired cells.

• Then get the result of this column.

• Finally, if you apply the steps forcolumn Dthen you will get the desired result.

See more information: [Solved]: Leading zero is not displayed in Excel (9 possible solutions)

Conclusion

From now on, follow the methods described above. Hopefully these methods will help you remove leading zeros in Excel. We would like to know if you can perform the task in a different way. ContinueExcelDemysite for more articles like this. Feel free to add comments, suggestions, or questions in the section below if you have any confusion or run into any issues. We will do our best to solve the problem or work on your suggestions.

Related Posts

• How to Use the Countable Number Format in Excel (4 Useful Methods)
• Change international number format in Excel (4 examples)
• How to Keep Leading Zeros in Excel Date Format (9 Quick Ways)
• How to Enter a 16-Digit Number in Excel (3 Easy Ways)
• Show Long Numbers in Excel (4 Easy Ways)
• How to Format a Number in Thousands and Millions in Excel (4 Ways)