An Easy Guide to Phone Number Format for Google Sheets

Despite Google Sheets being an incredibly powerful tool, it does have some annoying features. One is that phone number formats often get messed up by default. So, if you’ve ever wondered, “How do I format a phone number in Google Sheets?” Follow this phone number format Google Sheets guide to learn all there is to know.

Types of Phone Number Formatting

There are multiple ways in which numbers are formatted in the United States. Here are a few of the most commonly used phone number formats:

1. Domestic US Style: (123) 123-1234
2. Domestic US Style with dashes: 123-123-1234
3. International US Style: +1-123-123-1234
4. Letterhead Style US: 123.123.1234
5. Germany Dialed: 001-123-123-1234
6. France Dialed: 191 123 123 1234

Ways to Format Phone Number Google Sheets

There are essentially two ways in which you can format phone numbers in Google Sheets. The first method is a quick way to do it, but it has a few flaws. However, the second method uses the custom number format feature, allowing you to properly format the numbers in your spreadsheet.

Phone Number Format Google Sheets Using an Apostrophe

This is a straightforward workaround for formatting a number in Google Sheets, but this is only viable if you format a couple of phone numbers. This involves adding a plus sign and dashes in order to format the number in one of the predefined address book formats. Let’s look at how you can do this in your spreadsheet.

Here are the steps you need to follow to do so:

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

3. Add an Apostrophe ( ` ) symbol at the start.
4. Press Enter to check it worked

What Can Go Wrong With This Method?

When adding characters into the format, often, they can have an unintended effect. For example, in the example above, we added the plus and minus symbols interpreted by Google as a mathematical calculation that resulted in undesired output. But, by adding an apostrophe symbol, we are essentially telling Google Sheets to ignore the numbers and symbols.

Although this method of formatting a number in Google Sheets can be useful, it isn’t particularly useful when you have a huge list of phone numbers. For such applications, you will have to utilize the Google Sheets custom number format feature.

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

We talked about the different types of Google Sheets formats for a phone number. Converting these formats into a custom number format requires using characters already specified in Google Sheets. Depending on the number’s format, you will need to use various characters to signify the placement of the digits in the numbers.

Formula Techniques for Phone Numbers

Before we start, let’s look at some character examples we need to use in the custom number format. These are:

• # is used to represent the digit in its order of appearance.
• “” is used to display a text
• “ “ is used to add spaces
• “(“ and “)” are used to display the parentheses
• “-” is used to add dashes
• “.” is used to add a period
• “001” is used to add a predetermined number: 001 is an example, it could be any number.
• “+1-” is used to add the +1 international code followed by a dash. You could also type any other international call code in quotes instead.

How to Write a Phone Number Format Formula

Presumably, we know the phone number formats and how we can convert them into a format usable by Google Sheets. Let’s take a look at what each of the phone number formats we spoke about earlier will look like after it has been converted for use in Google Sheets:

1. US Style Domestic = “(“###”)”” “###”-“####
2. US Style Domestic with dashes = ###”-“###”-“####
3. US Style International = “+1-”###”-“###”-“####
4. US Letterhead Style = ###”.”###”.”####
5. Dialed from Germany = “001-”###”-“###”-“####
6. Dialed from France = “191 “”###” “###” “####

Let’s look at how you can use the custom number format to change the Google Sheets phone number format. The phone numbers we currently have in the example below are 10 digits.

Here are the steps you need to follow to change the phone number format in Google Sheets:

1. Click to select the cell(s) containing the unformatted sheet phone number. If there are multiple phone numbers, click and drag toward 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 drop-down menu will show up.
3. There, select Number and then click on Custom number format.

4. A separate window will open up in the middle of the screen. Enter your desired format in the Custom number format textbox. Feel free to copy and paste from our examples or create your own.
5. Click on the green Apply button on the right side to apply the format to the selected data.

How to Remove Existing Formatting from Phone Numbers

You can remove the custom number formatting from your spreadsheet but simply heading to the Format menu again and turning the formatting to Automatic instead of a Custom number format. Then you can also choose to remove or keep leading zeros.

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

Frequently Asked Questions

Is There a Phone Number Format in Google Sheets?

Google doesn’t have a proper 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 and then clicking on the Format button in the main toolbar. In the drop-down menu, select Number and then click on Custom number format. In the textbox, you can enter the number format.

How Do I Make a Phone Number Clickable?

You can make a phone number clickable by making it into a hyperlink and directing the hyperlink to a website that will allow you to dial the number. To do this, you will need to use the HYPERLINK formula. The syntax for 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-colored text indicating that the text is clickable. You could add extra cells with custom text like the above, or add the HYPERLINK function directly to the cell.

Final Word for Phone Number Formats in Google Sheets

Although the first method in this phone number format Google Sheets guide is easier in the short run, you’re better off learning how to use custom number formatting for phone numbers. Not only will that save you time with large spreadsheets, but you’ll also improve your overall spreadsheet skills.