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.

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.

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

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 ).

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

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

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.

So you will be as follows:

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).

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

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

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.

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 .

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

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.

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)

So you will add 0 before the phone number:

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 .

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

So you have added the number 0 to all phone numbers

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

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.

You will get the following:

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

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

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

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!