When you create a form you may want to include zeros in front of the number. For example

**000123**

But after you enter this value and press Enter it will immediately change to

**123**

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.

Contents

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

write **‘000123**

When you try to use the value from cell **A2** to add numbers it will be treated as a number.

## Custom formatting

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.

### TIP

You can format cells before entering numbers. In this case, the number **123** will automatically change to **000123.**

## Text function

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.

=TEXT(A2,"000000")

This will give us the following result.

## Concatenate function

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.

=CONCATENATE("000",A2)

The problem with this function is that it will add three zeros also to 0.

Let’s modify this formula a bit.

=IF(A2=0,"0",CONCATENATE("000",A2))

This formula will add zeros only if a value is different than 0.