This will automatically open a window from which we can choose the format we want to use. In this way, Excel shows us several predefined data formats. To display leading zeros, we will select «Custom», and in the box «Guy” we will introduce the format that we want to show.
For example, we can enter “00000,000″. This means that by default it will show us four leading zeros and three decimal zeros. Now, when we enter the data in our Excel we can see that they appear as follows.
If instead of four zeros to the left we want more or less to be displayed, we simply have to change the value that we have indicated before for the one we want. In the same way, we can modify the format so that units are shown at the end, or a greater or lesser number of decimals, thus being able to customize the tables according to our needs at all times.
Format cells as text
Another option that we can make use of is to change the format of a range to text. In this way, any data we enter will be treated as text values, even if they are numbers, causing Excel to keep the leading zeros in the numbers.
To do this we are going to select the range of cells in which we want to enter zeros to the left. Click on the Start tab and in the Number section, click on the drop-down section of number formatwhich by default appears as General and which we are going to change to the option Text.
When doing this, if we enter a figure with numbers with leading zeros they will not disappear because they are added as text values instead of as numbers.
Use a leading apostrophe
Another way we have available for zeros to appear on the left of a number is force Excel to add the number as text, using a leading apostrophe. In this way it is possible to keep those zeros while we add our data, which makes it a fast and easy method to use. We simply have to write an apostrophe before writing any number, which will take care of indicating to Excel that the data should be as text instead of as numbers.
As soon as we press Enter, the leading zeros will remain visible in our worksheet. Although the apostrophe will not be displayed, it is visible in the formula bar at the top, at the moment we select the active cell with the cursor.
With the TEXT function
Another way we have to show the leading zeros in Excel is through the TEXT function which will allow us to apply a custom format to any numerical data found in our spreadsheet
To do this, in the formula box we must enter the following command:
= TEXTO (Valor;Formato)
In this formula, we need to enter the value that we want to convert to text and apply the format that we want.
For example, if we want to add zeros to a number for cell B1, using the data in cell A1 so that the total number of digits is 6, we write:
=TEXTO (A1;"00000")
How to remove them
There are occasions where we can come across numerical data that contains leading zeros. In the event that we do not want to see them, we will see a couple of alternatives to eliminate those additional digits and thus be able to obtain the numerical value of the data.
with special glue
One way to remove leading zeros is through Paste Special. For this it will be necessary to fill a column with numbers 1 and copy the original values.
Then we click with the right mouse button on cell B1 and choose the option special paste, which will show us a new window. Here we must select the option Multiply and then clicking OK.
Now we check how we have removed the zeros, but Excel keeps the values of the cells aligned to the left. To remedy it, just change cell format to General or Number.
Clear zeros with the VALUE function
Another way that we have to be able to eliminate the leading zeros is to use the VALUE function, which is in charge of obtaining a numerical value represented as text. In this way, Excel downloads the zeros to the left of the numbers, so using this function we are going to eliminate the zeros to the left. If, for example, we select box B1 and write the formula:
VALOR=(A1)
Next, we will check how the leading zeros in column B1 have been removed.
