To change the cell formatting we have 2 options: first is Excel Format Cells option and second is Excel Text formula.
Excel Text function is used to convert a numeric value into Text format by using special format strings. You can combine numbers with text or symbols to display the data in a more readable format.
Syntax of Text function: - = Text (value, format text)
For Example: - cell A1 contains the number 1234
The function will return “01234”.
This function is formatting the cell A1 using a text format and ensuring that the number in the cell has 5 digits in it by putting 5 zeros in the brackets.
Let’s take an example and understand how to put a zero (0) in front of a number.
We have a list of STD numbers in column A, now we want to add a 0 in front of each number in this list.
To add the zero (0) in front of every number followthe below mentioned steps:
Add one zero in front of a number through “Format Cells”
To add the zero (0) in front of every number using the “Format Cells” option follow the below mentioned steps:
You can see that the cell has been formatted directly with a 0 in front of every number. These changes were done in column A itself. In the prior method where we added the 0 through formula, the changes were done in column B.
This formatting ensures that the data is entered in the right format in all the required cells / columns.
If you are working on importing Excel files on a daily basis & you want to prefix zero in front of the all the numbers but not text, then this article is for you. In this article, we will learn how to add zero in front of the number only.
Question): I have a column that contains text as well as numbers. What I want is a formula to update all the numbers present in the column with a zero in front of the number.
Following is the snapshot of data we have:
In this way, we can add zero number in front of the column to numbers only.
You may like:
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.