This sometimes happens when the elements come exported in the spreadsheet from another system, causing some kind of configuration crash.
It may seem like a minor problem, but for some it is annoying and hinders what you want to achieve or reflect on.
From simply converting measurements in Microsoft Excel to generating random numbers and letters in Excel. Almost anything is possible, so this too.
There are several alternatives that can be used for remove or remove zeros in Excel that have been placed on the left. Or if, on the contrary, what you are looking for is to add them, the following guide shows the steps to follow for these situations.
Remove zeros in Excel with special paste
When numeric data in Excel hanno Leading zeros after being pasted somewhere else will generally be left-aligned in cells.
This means that the program recognizes this data as text and not as numbers, which could be solved by changing the cell format. But it isn't, doing so won't work. What the user needs to do is perform a transformation of said data to remove zeroes in Excel.
In this way, let's assume the following case: imagine you have a number of five cells with 4-digit numbers, of which two are leading zeros.
Assuming they are in column A (A1 to A5), what you should do is fill cells from B1 to B5 with the number 1 in each.
Then, the values of column A are copied, then right click on cell B1 and the option «Copy special «.
You can use the best most useful keyboard shortcuts to use in Excel to speed up the work at your fingertips.
A pop-up window will then appear, in which you need to select an option that says » Multiply »And then« OK ».
As a result, you will get i original values, but without the remaining zeros. Although, these will still be justified, but now they can be corrected by changing the cell format.
Remove zeroes using the value formula
This process it is more direct and practical, and to explain it we will take as reference the previous example with the numbers in column A.
To remove zeros from each of these, we select the neighboring cell of column B and the following formula will be written in it: = VALUE (A1). This is in the case of column A. The same will be done with the other cells, and consequently the numerical data will be shown in the same way. without leading zeros.
Add zeros using cell formatting
You previously learned how to remove zeroes in Excel. But, if what you want is to add them, there are very easy ways to do it.
One of these ways is to change the cell format, then we select them and right click and choose " Cell format «.
This action will show a pop-up window and in the » Numeri »The last option called is selected» custom «.
There, on the right hand side, there will be a box that says " Type «, Will add as many zeros or digits as you want in the numeric data.
For example, if 5 zeros have been entered in the box and the value has two integers, the result will be 3 zeros to its left.
That is, if the number in cell A1 is 22 and the aforementioned number of zeros has been entered, you will get the following: 00022.
After configuring the amount you need, click on " Accept And the cells will have zeros on the left.
