Two options are always available for you whenever you want to format text on excel. You can either do it via Excel format cells option or by using the excel text formula.
You can convert a number into text or vice versa by adding special format strings. Adding zero in front of the text converts the text into a numerical string.
Below is the formula for adding zero in front of the text,
”= Text (value, format text)”
For instance, if the cell, A1 contains 1234, and you apply the function, =TEXT(A1, ”00000”), the function will display ”01234” as a result.
Take a look at =TEXT(A1, ”00000”)
The function will use the text format in formatting the values inside cell A1. There are five zeroes in the brackets, indicating that the result should have five values.
Now let’s consider an example:
Let’s consider a table containing STD numbers in the first column named ‘A.’ We will add 0 to the front of every number on the list.
Let’s follow the steps below:
Locate cell B2, select it and enter the formula; =TEXT(A2, “00000000000”)
Apply the formula by hitting the enter key
Immediately, the numbers will display with zero on the front of each number.
Press ‘CTRL+C’ to copy the formula.
Paste it into B3: B10
How to add zero in front of numbers by using ”Format cells option.”
As we mentioned earlier, you can add zero in front of each number. Follow the steps below:
1. First, locate the range of the mobile numbers and select.
2. Press the keys ”CTRL+1” for the ”Format cells” dialog box to pop up
3. Check the ”number tab” and select ”custom”
4. You see a box named ”Type”. Write ”0” eleven times.
5. Click Ok
Zeros are now in front of each of the numbers.
How to add zero in front of all the data that contain both numbers and texts.
We will achieve this by combining IF & ISNUMBER functions.
Enter the formula, =IF(ISNUMBER(A2)=TRUE,”0”&A2,A2) in cell B2.
All the values will display with zero added in front.
Was this tutorial helpful? I hope so. Kindly share this blog with friends and check again for new tutorials. Thanks for reading!