How to remove zeros in front of a number in Excel (6 easy ways) (2023)

If you are looking for the solution or some special tricks forremove zeros etc. pande of a numbertheyStanding outThen you have come to the correct place. There are many ways to remove leading zeros from a number in Excel. This article shows each step with proper illustrations so that you can easily apply it for your purpose. Let's get into the main part of the article.

Download the practical notebook

You can download the practical notebook here:

6 methods to remove zeros in front of a number in Excel

In this section I will show you 6 quick and easy methods toremove zerosin front of a number in Excel on Windows operating system. Here you will find detailed explanations of methods and formulas. I have usedMicrosoft 365 versionhere. But you can use any other version according to your availability. If a method doesn't work in your version, please leave us a comment.

1. Convert numbers in text format to numeric format to remove zeros

By default, numbers in Excel do not have leading zeros. You will see zeros in front of the numbers if the cells are in text format. You can convert formatting from text to numbers in several ways. I show 2 easy ways to do this.

1.1 Using the error checking option

Usually, numbers are converted to text format by adding aapostropheIn front of them. But there will be an error in them. just go topossibility of errorand convert them to numbers. I am showing the procedure step by step below.

📌 Stages:

• Initially,copy ofmito insertenter numbers with leading zerosSalidacolumn.
• After,chooseall cells in the output column.
• here you will see onefallain cells. FairclickI ham.

• Here you will find an option called "convert to numbers”. And choose it.

• You will see that all zeros in front of the numbers have been removed.

1.2 Change custom format for number

Again, sometimes the numbers in the cell have a custom format for certain digits. Then, to make the number from these digits, they add leading zeros. In this case, to remove the zeros it is necessary to change the format of the cells.

📌 Stages:

• First,chooseall cells offSalidacolumn.
• so go tolaraba.
• click cellformat settingsand selectNumeric format.

• As a result, you will seeleading zeroslet isremoteand the cells are insideNumeric format.But it will create somedecimal numberafter the number.

• If you likeremoveohdecimal zerosafter number thenchoosecells offSalidacolumn and go tonumeric formatopportunity again.
• ClickNo"decimal reducer"as many times as the number of decimal places you want to remove.

• Finally, you have numbers without anyprincipalmizeros to the right.

See more information:How to remove zero values ​​with formula in Excel (3 easy ways)

2. Use Excel formulas to remove leading zeros from a number

Excel has some functions that you can use to remove leading zeros from a number. Here I show 2 ways to use formulas.

2.1 Using the VALUE function

You can useVALUE functionFor this task. Follow the steps shown for this.

📌 Stages:

• First, paste this formula into the cell.C5.

• roll nowfilling handleicon to insert the applied formula in the other cells of the column or useExcel keyboard shortcuts Ctrl+CmiCtrl+Vcopy and paste.

similar readings

• How to Hide Zero Values ​​in an Excel Pie Chart (3 Simple Methods)
• Ignore Blank Cells In Excel Bar Chart (4 Easy Methods)
• How to ignore empty series in Excel chart legend
• Excel IFERROR function to return empty instead of 0

2.2 Combination of VALUE and TEXT functions

You can useTEXT functionasVALUE functionFor this task. Numeric cells remain in TEXT format. Follow the steps shown for this.

📌 Stages:

• First,to insertthis formula in cellC5

• After,to insertohsameformula for the other cells in the output column.

3. Multiply the numbers by 1

There is an interesting trick in Excel to remove leading zeros from numbers. Just multiply them by 1 and you'll see that all the leading zeroes are gone. Follow the steps below to get a better idea.

📌 Stages:

• First,writingto beFormulaand jealousC5:

• then usefill controller iconato insertformula similar to the other cells.

4. Use the Paste Special command

You also cancollar especialfunction to multiply cells with leading zeros by 1. Follow the steps below:

📌 Stages:

• First,to insertthe numbers withprincipal zerossell the column.
• After,insert 1in any cell outside of the dataset table.

• After,copy ofthe cell that contains 1.
• No,select all cellsdooutput columnand pressThe right button of the mouseHover over the marked cells toOpenohopportunities.

• Here you have to choose.collar especialpossibility.

• Now a new window called"Collar especial".
• choose"in"Nocollage options
• then selectpossibility of multiplicationand pressureBUENO.

• As a result, all zeros before the numbers are removed.

similar readings

• Use VLOOKUP to return blank instead of 0 or NA
• Hide Chart Series with No Data in Excel (4 Easy Methods)
• How to Hide Zero Values ​​in Excel PivotTable (3 Easy Methods)

5. Use the text wizard for columns.

You will find another great feature in Excel which istext to column. Follow these steps to use this feature for the task.

📌 Stages:

• choosecells offNumeric column.
• so go toDatatab and select "Text for column"possibility

• Notriune 1window, you must select"closed"and pressNextbutton.

• Nothrough 2window, you don't need to do anything, just touchNextbutton.

• In the last window you have to chooseC5cell in the last window. and pressSalidabutton.

• As a result, all zeros before the numbers are removed.

6. Use VBA code

I am sharing a VBA macro code to remove leading zeros from numbers in Excel. Follow these steps to apply and run the macro code.

📌 Stages:

• To do this, first go to the top track and press the buttondeveloperthen pressvisual basicsmenu selection

You can useAlt+F11Open"Microsoft Visual Basic for Applications"window if you haven't added the Developer tab.

• now called window"Microsoft Visual Basic for Applications"will appear. Here in the top menu bar press the button"To take"And a menu appears. You have to choose between them."Module"possibility.

• now a new one"Module"a window appears. ANDto insertthis VBA code in the box.

Sub delete_front_zeros_in_numbers () dim delete_range as rangedim work_range as range error resume nexttexttitleid = "excluir ceros na frente dos números" set work_range = application.selectionSet work_range = application.inputbox("range", xtitleid, work_range.address, type:=8) Work_Range.NumberFormat = "General"Work_Range.Value = Work_Range.ValueEnd Sub

• To run the code go to the top menu, press the buttoncarreraoption, and here opens some other options andchooseohExecute Sub/UserFormYou can also just pressF5to run the code.

• After clickingcarrera, it will ask you to select the range of cells to remove leading zeros from.
• Select the range of cells fromC5:C11from the worksheet and printBUENO.

• And you will see that the numbers do not have leading zeros.

See more information:How to Hide Rows with Zero Values ​​in Excel Using Macro (3 Ways)

Remove specified number of leading zeros from numbers in text format

All of the methods shown above will remove all leading zeros from a number. However, sometimes you may need to remove a certain number of leading zeroes. Here I will show you the procedure to remove leading zeros one by one.

📌 Stages:

• first let's eliminatemore to the left1 zero of the number. And if there are no leading zeros, the number will stay the same.
• Before,to insertthat number in the cellC5

• then dragfill controller iconto the right 3 cells. Then it will apply a similar formula to those cells. As a result, you will remove a leading zero from the numbers each time you apply the formula.

• Then select the cellsC5:F5and dragfill controller iconto the last row of the data set to apply a similar formula to the cells.

• As a result, you will see that leading zeros are removed one by one whenever possible. And in the last column, all leading zeros are removed.

Conclusion

In this article, you found out how to remove leading zeros from a number in Excel. I hope you found this article useful. You can visit our websiteExcelDemyto learn more about Excel-related content. Please leave comments, suggestions or questions if you have any in the comments section below.

Related Posts

• Hide Zero Data Labels in an Excel Chart (4 Easy Ways)
• How to use XLOOKUP to return blank instead of 0
• Leave Cell Blank If No Data In Excel (5 Ways)
• How to use VLOOKUP to return a blank space instead of 0 (7 ways)