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
=TEXT (A1,"00000")
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.

**Example:**

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:

- Select the cell B2, and write the formula.
- =TEXT(A2,"00000000000")
- Press Enter on the keyboard.
- The function will return the mobile number with the added zero in front of the number.
- Now copy the formula by pressing the key “CTRL+C” on the keyboard and paste in the range B3:B10 by pressing the key “CTRL+V”.

**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:

- Select the range of mobile numbers.
- And press the key “CTRL+1” on the keyboard.
- The “Format Cells” dialog box will appear..
- In the “Number” tab select “Custom” from the list.
- In the “Type” box write 0 eleven times.
- Click on ok.

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:

- To get the result, we will use a combination of
**IF & ISNUMBER** - In cell B2 the formula is
**=IF(ISNUMBER(A2)=TRUE,"0"&A2,A2)**

In this way, we can add zero number in front of the column to numbers only.

**You may like**:

Terms and Conditions of use

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.

Saved me a lot of time. Great article.

Thank you so much. Bless!!

Exellent

How to add this eg 2,5,4a,6,7,3d

Ans should be 27 while addition we should neglect a and d

Hi,

You can split numbers from string and sum them up. You can use this article,

https://www.exceltip.com/excel-text-formulas/split-numbers-and-text-from-string-in-excel.html

thanks so much! I've lost so much time trying to manipulate text to columns to get this done. amazing!

good job

Maybe this helps, I tried it seems to work out fine.

select the cells

Cell properties

custom : \0#

It will add a 0.

when saving to .csv and rename to file to .txt you will see the 0 added.

Name,Phone

Name1,0333

Name2,04444

Name3,0666666

Name 4,055555

Name5,088888888

above ones with \0#

below is default

Name1,333

Name2,4444

Name3,666666

Name 4,55555

Name5,88888888

Thank you for helping me solve this issue.

Thanks!

What if i wanted to add the 0 in front of text?

use Concatenate Function to add 0 in front of a text

=concatenate("0",text)

or

use

="0"&"text"

understand concatenate function here

https://www.exceltip.com/tips/concatenate-formula-in-microsoft-excel.html

how to convert later 254

19-20/ 254 formula pls helpme

You can use formula

=CONCATENATE ("19-20/ ",number)

or

"19-20/ "&number

THNX

this is not working in google sheet same result comes up

Try =text(cell number,"the zeros you need"). That should work. I just did this in google sheet

what if there are 2 types of digit numbers. 1 hv 10 digits, and other 11 digits. how to seperate it?

I want to use the zero in front of any number only in some cells, those are account numbers. Since the accounts belong to different banks so they all needn't zero in front of them.

This means What I type that should remain in that format only, like Word. But while I use TEXT format in Excel, it converts them in SCIENTIFIC format, Then I've to click each of them to correct formatting of each such cell.

Isn't that possible to make a formula to convert the numbers (in the pasted sheets of 'Word' into 'Excel') starting with a special number must have zero & remains not?

THANKS FOR THE INFO 🙂

THANK YOU SO MUCH...

Right click on the tab—>Select Format Cells->select Custom->Select 00000->zero will be inserted before the number.

Right click on the tab--->Select Format Cells->select special->Select zip code->zero will be inserted before the number.

Hi I need help with home work and I cant do box methid sorry

This is great, thank you.

Very helpful technique !

non of these are working

What if the cell you want to add a 0 in front of includes numbers and text? For example, I want to add a 0 in front of:

909 - Design Project Engineer

910 - Design Project Engineer II

You can use concatenation, explained here in detail.

https://www.exceltip.com/excel-text-editing-and-format/join-first-and-last-name-in-excel.html

i love u so much thank you 🙂

thanx so much..you have made my work easier.

thanx for the quick solution 😉 you saved mah a lot of time