There are many problems that could manifest in Excel while you’re working on a task.
A common problem that many people come across when using this program is the appearance of leading zeros.
Leading zeros are zeros that appear immediately in front of the value (or multiple values) of a cell.
Zeros can appear when issues with formatting arise – particularly if a numerical value is being formatted as text or cell formatting dictates that a certain amount of digits be shown.
When they’re unwanted, leading zeros can cause a variety of issues to arise and can make getting things done a hassle.
As such, finding a way to get rid of unwanted zeros will be critical if you want to remove them.
Excel usually removes leading zeros automatically, yet sometimes it might not – leaving zeros in front of the values within a cell.
How to Remove Leading Zeros in Excel
Although leading zeros can make working in Excel more difficult, this problem can be corrected with the right solutions.
Below are a few tips that you can use right away to get rid of leading zeros.
Change the Formatting Setting to Get Rid of Leading Zeros
Cells may sometimes be formatted to make numerical values such as numbers have a certain amount of digits – resulting in leading zeros in front of them.
Changing the formatting of the cells is a quick way to eliminate zeros that are visible within cell contents.
- Click and select the cells which contain the leading zeros.
- Press and click the ‘Home’ tab in the ribbon menu.
- Select ‘Number Format’ in the dropdown menu near the top right of the ribbon menu.
- Click on ‘General’.
A simple change in format can be the answer to getting rid of any leading zeros in your spreadsheet.
Apply the Value Function Method
This method uses the value function, which converts text into a numerical value, such as a number.
The value function is good for a lot of different uses – including taking away zeros where you don’t want them to appear.
This technique can be especially helpful since it can address more causes of leading zeros – making solving this issue easier to accomplish.
- Identify the cells which contain unwanted zeros in front of the numbers.
- Apply the formula =VALUE(A1) to the cells that contain the leading zeros.
Upon using the value function as specified above, the zeros in front of the specified values should disappear.
Implementing the Text to Columns Method
The Text to Columns option is commonly used to separate the contents of a cell and put them into multiple cells.
This feature would be ideal if you have different values within a cell that you would like to split apart.
The Text to Columns method is also among one of the quickest and easiest to use – making it an ideal method to try.
- Click and drag to select the cells which have leading zeros within them.
- In the ribbon menu, select and click on ‘Data’.
- With the ‘Data Tools’ area, select ‘Text to Columns’.
- When the ‘Convert Text to Columns’ wizard pops up, click ‘Delimited’ then select ‘Next’.
- Make sure to deselect each delimiter, then select ‘Next’.
- Select the destination cell(s) then press ‘finish’ to complete the process.
After using the Text to Columns approach, you should easily be able to remove any unwanted leading zeros.
The Error Checking Method
The error checking option is useful for finding errors involving formulas or other errors that may be hard to catch.
Using the error checking method will work for cells that contain numbers, not text.
It can also be an effective way to turn numbers that have been converted into text back to numbers.
- Click and select the cells which contain the numbers with leading zeros in front of them. At this point, you might see a yellow icon with an exclamation point pop up next to one or some of the selected cells.
- Press on this yellow icon.
- Select the ‘Convert to Number’ option.
After pressing ‘Convert to Number’, the error checking technique should help you to get rid of any leading zeros.
Use Paste Special to Add Numbers in Cells to 0
Leading zeros can be solved by adding numbers within cells to 0.
This technique will involve using the ‘Paste Special’ option.
Using the ‘Paste Special’ method can be a very simple way to get rid of the leading zeros within a spreadsheet.
- Click on an empty cell, then right click and select ‘Copy’ or hold CTRL + C.
- Click and select the range of cells which contain the leading zeros that you want to get rid of.
- Right click, then select the option ‘Paste Special’.
- When the ‘Paste Special’ dialog box pops up, select “Add” under “Operation”.
- Next, click on ‘OK’.
Adding the selected cells by 0 should then remove any leading zeros within them.
