Phone Numbers in Excel

Let`s cover 3 examples of issues you might have with phone numbers in Excel and check how you can use the tool “Custom Format” to solve those issues.

Typical Issues:

Inserting phone numbers in Excel may take a lot of your time if you try to match the format you need manually. In our example below, we have 3 different styles of phone numbers to work with (2 different types of local phone numbers and 1 international phone number). Let`s try to copy and paste the numbers we need by taking the following steps:

1. Copy the range C20:C29 and paste it as “values” in the range C5:C15. You can then notice a few issues here. These numbers are not following the convention required which is a block of 4 digits, followed by a block of 3 digits and another block of 4 digits with all the spaces between the blocks. It`s also omitting the number 0 at the beginning which is a requirement for the phone numbers of this region.

2. Now copy the range F20:F29 and paste it as “values” in the range F5:F15. The same issues from the first sequence appear here again. In addition to that, you now have 2 decimals displayed at the end of your phone number which is meaningless.

3. Finally, copy the range I20:I29 and paste it as “values” in the range I5:I15. We can see the format of that style is now even worse than the others, as it shows 4 decimals at the end and the thousand separators.

So, let`s now check how to correct these issues by customizing the format styles we need.

Local Phone Numbers (Region A)

To customize the first number of the first sequence (National Phone Number of Region A), take the steps below:

1. Select cell C5 and press the shortcut CTRL 1 and the “Format Cells” menu will pop up.

2. Regarding the number style, click on the last option “Custom” which is the option which allows you to customize numbers.

3. Next, you need to create the “generic format” required by inserting a sequence of number zeros. So, the phone number convention for this region A, is a block of 4 digits, followed by a block of 3 digits and then a block of 4 digits again. Therefore, in the field “Type” you need to type the number 0 four times, press space, type number 0 three times, press space again and type the number 0 four times again. The format you designed will be already displayed in the field “Sample”.

4. Once you are happy with that custom format, just click on OK to format cell C5.

5. You can then copy that cell, select the entire range C5:C14, and press CTRL ALT V to open the Paste Special menu.

6. In that menu, mark the option “Formats” and press ENTER. The entire sequence will then be formatted exactly with the phone number you need.

Local Phone Numbers (Region B)

A similar process can be done to other lists. So, for the national phone numbers of region B take the following steps:

1. Select cell F5, open the “Formats Cells” menu (shortcut CTRL + 1) and select the option “Custom” at the bottom part of that menu.

2. The number convention we need in this example is a block of 3 digits, followed by 2 blocks of 4 digits each. Therefore, in the field “Type”, you need to type the generic sequence 000 0000 0000.

3. Confirm in the field “Sample” that the number to be formatted matches the phone number convention you expect for that region and then press ENTER.

4. Now, copy cell F5, select the entire range F5:F14, open the Paste Special menu (shortcut CTRL ALT V), choose “Formats” and press ENTER. Your entire sequence will be formatted for you.

International Phone Numbers

The last example is related to international phone numbers which requires the country code to be inserted in brackets together with a plus sign in addition to the usual formatting for the blocks of digits. So, follow the steps below:

1. Select cell I5, open the Format Cells menu (i.e. CTRL 1), and choose “Custom”.

2. In this case we first need a country code (i.e. a block of digits, in brackets and with a + sign) and one block of 4 digits followed by two blocks of 3 digits each. Therefore, in the field “Type”, you need to insert the generic format: (+00) 0000 000 000.

3. Check that the field “Sample” correctly reflects the number convention you need and then click on OK.

4. Now copy cell I5 and paste as “Formats” for the entire range I5:I14 using the Paste Special command (shortcut CTRL ALT V). Your entire list of international phone numbers will be clearly displayed.

Important Note:

In this tutorial we covered the format customization of phone numbers for sake of background for the explanations. However, keep in mind that you can apply this customization to any type of information you face at work. So, make sure to practice this important tool.