Excel: How to put 0 in front of numbers

Two options are always available for you whenever you want to format text in excel. You can either do it via the 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.

How to put 0 in front of numbers use the Text Function

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, "00000000")

Apply the formula by hitting the enter key

Immediately, the numbers will display with zero on the front of the number.

Press 'CTRL+C' to copy the formula. Paste it into B3: B10. Alternatively, You can right-click and drag the green rectangle to the rest of the cells.

How to add zero in front of numbers by using the “Format cells option.”

You can add zero in front of each number, as we mentioned earlier. 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" eight times.

5. Click Ok

Zeros are now in front of each of the numbers.

Using the Apostrophe (') at the Start


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.

1. Enter the formula, =IF(ISNUMBER(A2)=TRUE,"0"&A2,A2) in cell B2.

2. Right-click and Drag the green rectangle downwards, and 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!

%d bloggers like this: