How to add zeros to phone numbers in Excel

By default, when you enter a phone number or a sequence of numbers with a leading zero, all zeros at the beginning of that number will be lost. So if you want to enter the phone number, it will not display the leading zeros. So how can I add zeros to numbers in Excel? Invite you to follow the article below.

How to add zeros to phone numbers in Excel Picture 1

Here are 5 ways to add zeros to phone numbers in Excel, you can choose the appropriate way to do it.

Method 1: Convert cell format to text

First, you type the apostrophe 'into the box and then enter the phone number you want.

How to add zeros to phone numbers in Excel Picture 2

So your phone number will be displayed with both leading zeros.

How to add zeros to phone numbers in Excel Picture 3

Or if you need to enter multiple phone numbers, you format all cells to enter the phone number by:

1. Select the cells to format text, then right-click and select Format Cells ( Ctrl + 1 key combination ).

How to add zeros to phone numbers in Excel Picture 4

2. In the Format Cells dialog box , in the Number tab, select Text in Category and click OK .

How to add zeros to phone numbers in Excel Picture 5

3. Enter the phone number in the cells you have formatted.

How to add zeros to phone numbers in Excel Picture 6

Doing this way you will see a green tick on the top left of the box, when selecting cells will appear yellow warning signs. To turn off the warning, select the box and select 1 or select all the cells, next select the yellow warning icon and select Ignore Error.

How to add zeros to phone numbers in Excel Picture 7

So you will be as follows:

How to add zeros to phone numbers in Excel Picture 8

Method 2: Set the format '0' #

Step 1 : Select all the cells you want to enter the phone number and right-click and select Format Cells (Ctrl +1).

How to add zeros to phone numbers in Excel Picture 9

Step 2 : In the Format Cells dialog box, select Custom and enter '0' # in the Type box and then click OK .

How to add zeros to phone numbers in Excel Picture 10

So you can enter the phone number containing the first 0 without losing.

How to add zeros to phone numbers in Excel Picture 11

Method 3: Set up Custom and separate phone numbers arbitrarily

To add zeros and separate phone numbers, do the following:

Step 1: Select the cells to enter the phone number and right-click and select Format Cells.

How to add zeros to phone numbers in Excel Picture 12

Step 2 : In the Format Cells dialog box , in the Number tab, select Custom and enter the setting in Type is 0 ### ### #### or you can customize it as you like (for example 0 # #### ##### or 0 ## ## ## #### or 0 ### ## ## ###) and press OK .

How to add zeros to phone numbers in Excel Picture 13

So when you enter the phone number, there will be zeros and number separators according to the option you have set.

How to add zeros to phone numbers in Excel Picture 14

Method 4: Add the - or signs. or spaces between phone numbers

In the process of entering phone numbers, you can use hyphens, dots or spaces between numbers to convert numeric data into a string so you will enter the number before the phone number.

How to add zeros to phone numbers in Excel Picture 15

Method 5: Use the CONCATENATE function

If you have already entered a list of phone numbers but lost the leading zero, you can use the CONCATENATE function to add 0 to the beginning of the phone number instead of having to re-type the whole phone number. . You add 0 to your phone number as follows:

Step 1: Create a full phone column, in the first cell in this new phone column you enter the function = CONCATENATE ("0"; B6)

How to add zeros to phone numbers in Excel Picture 16

So you will add 0 before the phone number:

How to add zeros to phone numbers in Excel Picture 17

Step 2 : Copy the formula to the bottom cells by dragging the mouse to the bottom right of the cell containing the function with a + sign .

How to add zeros to phone numbers in Excel Picture 18

Hold down and drag down the rest of the cells to copy the formula.

How to add zeros to phone numbers in Excel Picture 19

So you have added the number 0 to all phone numbers

How to add zeros to phone numbers in Excel Picture 20

Step 3: After adding, copy the full phone column.

How to add zeros to phone numbers in Excel Picture 21

Step 4 : Paste the full phone number into the old phone number column, you choose to paste data as shown below to not paste the whole formula into the old phone number column.

How to add zeros to phone numbers in Excel Picture 22

You will get the following:

How to add zeros to phone numbers in Excel Picture 23

Step 5 : Next you need to delete the phone number column containing the function formula.

How to add zeros to phone numbers in Excel Picture 24

To hide the blue warning in the old telephone column, select all phone numbers and click the yellow icon -> Ignore Error.

How to add zeros to phone numbers in Excel Picture 25

So you have added the number 0 to the phone number.

How to add zeros to phone numbers in Excel Picture 26

The above are ways to add zeros to phone numbers in Excel, so you can enter the phone number into Excel without losing zero. I wish you success!