When you create a form you may want to include zeros in front of the number. For example
But after you enter this value and press Enter it will immediately change to
This happens because the default data type of a cell is general. This means that Excel is trying to guess which data type you are trying to use.
In this example, it treats it as a number because all characters inside cell B2 are numbers.
Forcing Excel to treat value as text
If you want Excel to treat a value as text and not as a number, function or any other value, insert (’) at the beginning of the phrase.
Instead of writing 000123
When you try to use the value from cell A2 to add numbers it will be treated as a number.
If you already inserted the numbers to which you want to add leading zeros, you can use the custom formatting.
First, select the numbers.
Use right-click to open the contextual menu (Ctrl + 1). Got to Custom and enter the desired number of zeros.
For example, if you enter 000000 then in the cell where you have 123 you will have 000123.
This is the result.
Because the value in cell A6 is longer than that the number of zeros – 1, it won’t add any leading zeros.
You can format cells before entering numbers. In this case, the number 123 will automatically change to 000123.
Instead of custom formatting, you can use the TEXT function. This function will convert a value to text using the specified number format.
Let’s simulate earlier example with this function using the following formula.
This will give us the following result.
If you want to add a specified number of leading zeros, you can use the CONCATENATE function.
The following formula will add 3 leading zeros to every value.
The problem with this function is that it will add three zeros also to 0.
Let’s modify this formula a bit.
This formula will add zeros only if a value is different than 0.