An Easy Guide to Phone Number Formatting in Google Sheets

by bolamarketing.com

Google Sheets is an incredibly powerful tool, but it does have some frustrating features. One of them is that phone number formats are often misinterpreted by default. So, if you’ve ever wondered how to format a phone number in Google Sheets, follow this phone number formatting guide to learn everything about it.

Types of Phone Number Formatting

There are several ways to format numbers in the United States. Here are some of the most commonly used phone number formats:

1. Domestic Style in the United States: (123) 123-1234
2. Domestic Style in the United States with Dashes: 123-123-1234
3. International Style in the United States: +1-123-123-1234
4. Letterhead Style in the United States: 123.123.1234
5. Calling from Germany: 001-123-123-1234
6. Calling from France: 191 123 123 1234

Methods of Phone Number Formatting in Google Sheets

There are essentially two ways to format phone numbers in Google Sheets. The first method is quick but has some drawbacks. However, the second method uses the custom number formatting feature, which allows you to correctly format numbers in your spreadsheet.

Formatting Phone Numbers in Google Sheets Using an Apostrophe

This is a simple solution for formatting a number in Google Sheets but is only viable if you need to format a few phone numbers. It involves adding a plus sign and dashes to format the number in one of the predefined address book formats. Let’s see how you can do it in your spreadsheet.

Follow these steps:

1. Click on the cell containing the unformatted phone number.
2. Follow the formatting instructions and add the characters for the format. In this example, we’ll follow the international US phone number format. Make sure not to press the “Enter” key here.

3. Add an apostrophe (‘`’) at the beginning.
4. Press “Enter” to check if it worked.

What Could Go Wrong with This Method?

When you add characters in the format, they can often have an unintended effect. For example, in the above example, we added the plus and minus symbols, which Google interpreted as a mathematical calculation resulting in an undesirable outcome. But by adding an apostrophe, we essentially tell Google Sheets to ignore the numbers and symbols.

While this method of formatting a number in Google Sheets can be handy, it’s not particularly practical when you have a long list of phone numbers. For such applications, you will need to use Google Sheets’ custom formatting function.

How to Format Phone Numbers in Google Sheets Using Custom Number Formatting

We have discussed the different types of phone number formats. To convert these formats into a custom number format, you need to use characters already specified in Google Sheets. Depending on the number format, you will have to use different characters to indicate the placement of digits in the numbers.

Formula Techniques for Phone Numbers

Before we begin, let’s look at some examples of characters we need to use in the custom number format. They are:

• # is used to represent the digit in its appearing order.
• "" is used to display text.
• " " is used to add spaces.
• ( and ) are used to display parentheses.
• - is used to add dashes.
• . is used to add a dot.
• "001" is used to add a predetermined number; 001 is an example, it could be any number.
• "+1-" is used to add the international code +1 followed by a dash. You can also type any other international call code in quotes.

How to Write a Phone Number Format Formula

Suppose we know the phone number formats and how to convert them into a usable format in Google Sheets. Let’s take a look at how each of the phone number formats we mentioned earlier will be converted for use in Google Sheets:

1. Domestic Style in the United States = ("(")###(") ")###–####
2. Domestic Style in the United States with Dashes = ###–###–####
3. International Style in the United States = "+1-"###-###-####`
4. Letterhead Style in the United States = ###“.”###“.”####
5. Calling from Germany = "001-"###-###-####`
6. Calling from France = "191 "“”###” “""###" "####`

Let’s see how you can use custom number formatting to change the format of phone numbers in Google Sheets. The phone numbers we currently have in the example below consist of 10 digits.

Follow these steps to change the format of phone numbers in Google Sheets:

1. Click to select the cell(s) containing the unformatted phone numbers. If there are multiple phone numbers, click and drag in the direction of the other cells to select them. This will be indicated by the blue border around the cells.
2. Once selected, click on the “Format” option in the main toolbar. A dropdown menu will appear.
3. There, select “Number” and then click on “Custom number format”.

A separate window will open in the center of the screen. Enter the desired format in the “Custom number format” text box. Feel free to copy and paste from our examples or create your own.

Once you have entered the desired format, click on the green “Apply” button on the right side to apply the format to the selected data.

How to Remove Existing Phone Number Formatting

You can remove custom number formatting from your spreadsheet by simply accessing the “Format” menu again and changing the formatting to “Automatic” instead of a custom number format. Then, you can also choose whether to remove or keep leading zeros.

If you have imported a sheet and want to remove unwanted phone number formatting, you can use the REGEXREPLACE function to remove unwanted dashes and spaces.

FAQs

Is there a phone number format in Google Sheets?

Google does not have a specific phone number format that you can use in your spreadsheet. However, Google allows you to create a custom number format. You can do this by selecting the cell containing the unformatted phone number, then clicking the “Format” button in the main toolbar. In the dropdown menu, select “Number” and then click on “Custom number format”. In the text box, you can enter the desired number format.

How to make a phone number clickable?

You can make a phone number clickable by turning it into a hyperlink and directing the link to a website that will enable you to dial the number. To do this, you will need to use the HYPERLINK formula. The syntax of the formula is =HYPERLINK(siteURL, label).

In the formula, add this link https://ctrlq.org/call/ and reference the cell containing the phone number, like &B2 in the example above. When the formula is executed, you will see blue text indicating that the text is clickable. You can add additional cells with custom text as shown above, or directly add the HYPERLINK function to the cell.

Conclusion

While the first method in this phone number formatting guide may be easier in the short term, it’s better to learn how to use custom number formatting for phone numbers. Not only will it save you time with large spreadsheets, but it will also improve your overall spreadsheet skills. So don’t wait any longer, use these tips to easily format phone numbers in Google Sheets! Visit Crawlan.com for more tips and tricks like this.