Excel, that dreaded word for many because when they hear it they imagine hundreds of functions, formulas, tables and other elements difficult to understand and perform. This is because there is no awareness of the capabilities of these integrated Excel functions which are there to improve our lives and help us make each task much faster, organized and with the best results..
Microsoft has developed Excel, in this case we talk about the 2016 and 2019 edition, as a set of features that boost data management. But there are still certain default values ​​that if we do not know how to handle them, they can give error or display the expected results and one of these is that when the given number or that we must enter has a zero on the left. Excel automatically deletes it, for example, if we enter the following numbers:
0100050010000785
We will see this in the spreadsheet:
As we can see, Microsoft Excel 2019 and 2016 will automatically eliminate the leading zeros and if we have entered large numbers, they will be converted to scientific notation. This is done nothing more and nothing less so that the formulas and each of the mathematical operations integrated in Excel can work in the correct way.
Now, well, in other cases we do not always seek to eliminate the zeros but add them for some administrative task and for this reason TechnoWikis will explain through this tutorial how to perform these tasks in a functional but striking way.
How to remove leading zeros in Excel 2019 or 2016
For this example we have entered data that have two leading zeros in the range of A: 1 to A: 4:
When the data in the cell is on the left, it means that Microsoft Excel has taken these values ​​with the text format, even worse if we modify the format to “Number†we would not see any changes there then here TechnoWikis takes action to clear your doubts..
To keep up, remember to subscribe to our YouTube channel! SUBSCRIBE
1. Remove remove leading zeros in Excel with special paste
Step 1
To achieve this goal, first of all we will insert the number 1 in column B in the same range:
Step 2
This is done in order to multiply the range of column A with the range of column B, we proceed to select and copy the range A1: A4:
Step 3
Once copied, we will go to the Paste Special option located in the "Clipboard" section or by right clicking on cell B1 and then select Paste Special:
Step 4
In the displayed window we activate the “Multiply†box located in the Operations section:
Step 5
Click on OK and we will see that the zeros of the selected cells have been eliminated:
There we can click on the “Decrease decimals†icon in the Number group so that the integer values ​​remain.
2. How to remove leading zeros in Excel using the Value function
Now we can use the integrated functions of Excel to automate the process and one of the ideal functions for this is the Value function which is responsible for converting text to number.
Step 1
Its use is simple, so you lose the fear of using the functions, since we simply have to use the following formula in cell B1:
= VALUE (A1)
Step 2
We drag the formula to the other cells in the range and this will be the result obtained:
As we can see, the Value function extracts the text from the selected range..
3. How to put leading zeros in Excel using cell format
If the issue you need is to add zeros instead of eliminating them, Excel also offers the practical mechanisms for this, not always the values ​​of a cell will be used in formulas so that we can add one or more zeros as the case may be.
Step 1
To use this option, we select the range in which we have to add the zeros, right click on one of the cells and select Format Cells:
Step 2
The following window will be displayed, there we must go to the Number tab and in the category options we choose “Customâ€, then, in the “Type†field we will enter the desired number of zeros taking into account that the numbers already assigned must be assumed:
Step 3
In the Sample section we can see the final result, we click on Accept to complete the process:
4. How to put leading zeros in Excel using the Text function
This function has the objective of converting a numerical value into text and it is required to specify the appropriate format, so to make use of this function we will enter the selected cell and the format with the desired number of zeros like this:
= TEXT (A1; "000000")
We drag the formula and this will be the result obtained:
5. Keep the leading zeros using the Text format Excel 2016 and Excel 2019
An additional point in allowing Excel to assume the zeros on the left of the cell is to set the Text format.
Step 1
To do this we simply select the range where the numbers will be and in the Number group we display the formatting options and select Text:
Step 2
Then we can enter the numbers with the zeros without any problem in the spreadsheet:
With any of the methods explained by TechnoWikis it will be possible to add and eliminate the zeros that may be present in our spreadsheet and thus work as appropriate.