Let’s discuss the Google Sheets phone number format. It’s an often overlooked but handy tool, because it helps to very quickly identify contact information. I’ll show you a few different methods for how to format phone numbers in Google Sheets. The first is the simplest. Just navigate to Format > Number > Custom number format and select the type of formatting you want.
In my tutorial, I’ll also show how to apply the Google Sheets phone number format across a whole row or column and how to create custom phone number formats. I include screenshots for clarity, and I break down formulas into individual parts so you understand the “why” in addition to the “how”. Ready to get started?
This Article Covers:
How To Format Phone Numbers In Google Sheets
In this tutorial, I’ll add standard United States domestic phone number formatting to sample unformatted phone numbers. The term “unformatted” refers to working with phone numbers as only digits: there’s no existing formatting like dashes or dots. Here’s a screenshot that shows unformatted phone numbers. So how do we get them in the proper format?
Follow along with my screenshots to see the easiest way. This will show you to format phone numbers in Google Sheets with unformatted data. Remember that my example uses the United States domestic format: (123) 456-7890. I’ll show you how to use other formats later (including your own custom formats).
- Highlight the column you want to format. In the example, we’ve highlighted column B.
- Open the “Format” drop menu, choose “Number,” select “More Formats”, and pick “Custom number format.”
- Enter the format formula in the “Custom number formats” field and select “Apply.” In the example, we’re using the formula “(“###”)”” “###”-“####
- The column will now format phone numbers accordingly.
Sometimes you may have to work with phone numbers that are already formatted but are using the wrong format. Below, I’ll teach you how to remove formatting from phone numbers if you received them in an incorrect format. I’ll also show you how to add a 1 to a phone number in Google Sheets. That’s specifically useful when you want to include a country code.
How to Write a Phone Number Format Formula
There are lots of different ways to format phone numbers in different contexts across the globe. This tutorial uses common formats for United-States-based phone numbers as an example. But before we move on, let’s talk about a few variations. Below, notice the difference in how we separate the area code from the rest of the digits. I’ve also included a few international phone number formats, which include country codes.
Examples of Phone Number Formats
- Style: formula = example
- US Style Domestic: “(“###”)”” “###”-“#### = (123) 456-7890
- US Style Domestic with dashes: ###”-“###”-“#### = 123-456-7890
- US Style International: “+1-”###”-“###”-“#### = +1-123-456-7890
- US Letterhead Style: ###”.”###”.”#### = 123.456.7890
- Dialed from Germany: “001-”###”-“###”-“#### = 001-123-456-7890
- Dialed from France: “191 “”###” “###” “#### = 191 123 456 7890
Formula Techniques for Phone Number Formats in Google Sheets
I was completely overwhelmed when I first saw the Google Sheets custom number formatting system. That changed once I understood how to handle functions. Fortunately, phone numbers tend to be straightforward, so you only need to use the system to add pre-determined characters at specific points in the formula. Let’s talk about how to handle numbers and characters.
Handling Numbers
The formula system uses “#” to designate the in-order digit in the formatting formula. The first “#” references the first digit, the second “#” references the second digit, and so on. That means, if you’re formatting 10-digit phone numbers, you’ll be using “#” 10 times in your formula.
Handling Characters
Depending on the format, you’ll be adding parenthesis, dashes, periods, spaces, numerical prefixes, and other textual information.
And remember, Google Sheets uses double quotation marks to designate textual information between numbers. For example, if you want to add a dash between two digits, you would use a formula like #”-”#.
If you applied this formula to a cell with the value “21” it would display like “2-1.”
Example character key:
- # = represents the next digit in order of appearance.
- “” = display text
- “ “ = add a space
- “(“ and “)” = display parentheses
- “-” = add a dash
- “.” = add a period
- “001” = add pre-determined numbers
- “+1-” = add a +1 international code followed by a dash.
Google Sheets Phone Number Format Using A Single Quote
I love sharing spreadsheet hacks, and here’s one of the easiest. You can apply the phone number format in Google Sheets with a single quote. Want to know how? I took a few screenshots to show the process, but it breaks down into a simple process. Basically, you add a single quote in front of a string of numbers. That tells Google Sheets you want to use the phone number format, and it takes care of the rest. Here’s what that looks like:
- Go to the cell with your phone number.
- Change the format to the phone number format required, for example US Style Domestic.
- In the formula box, add an apostrophe before the phone number
- Click Enter.
Note that this works best when you’re working with a small data set. The single quote method formats your cell, but it’s a little more annoying when you want to apply that same format across a range of cells. So let’s talk about removing formatting. Note that first you may want to convert cell text to digits.
How to Remove Existing Formatting from Phone Numbers
If you’re working with a noisy data set, full of multiple formats, you’ll want to identify and replace all existing formats with your chosen format. So here’s how to remove existing formatting when some numbers have formatting and others are just digits.
Instead of trying to come up with a formula that accounts for all sorts of phone number formats, it’s easier to just remove all existing formatting and start fresh. I like to use the VALUE and REGEXREPLACE functions for this reason.
Here’s my formula that removes existing formatting from cells:
=VALUE(REGEXREPLACE(*selected cell*,"[^[:digit:]]", ""))
It specifically removes all non-digit values from a cell. If you’ve already made sure your cells contain numbers, this removes dashes and parenthesis so everything is uniformly unformatted.
Here’s a real-world example (with fake phone numbers). It shows how to remove the formatting from these three pre-formatted numbers.
- Create a helper column next to the preformatted numbers by right-clicking on the column and selecting “Insert 1 column right.”
- Name the column as “Helper column”
- Enter the formula
“=VALUE(REGEXREPLACE(CELL,"[^[:digit:]]", ""))
in the helper column. The example uses the formula
“=VALUE(REGEXREPLACE(D2,"[^[:digit:]]", ""))”
in cell E2 to reference cell D2.
- Drag the formula down the column. This will populate the rest of the column with unformatted phone numbers.
- Highlight the phone numbers from the helper column and select “Copy” from the right-click menu.
- Choose the original column phone number values, right-click, select “Paste special,” and “Paste values only.” You can also use the keyboard shortcut CTRL + SHIFT + V for Windows and Cmnd + SHIFT + V for Mac.
- Google Sheets will populate the original column with unformatted phone numbers.
So this is how you can use simple custom formatting to format phone numbers in Google Sheets. That leaves everything unformatted, so you can highlight everything and choose the phone number format from the custom number format menu I mentioned earlier. Note that you can use the format painter for this too.
As a reminder, you can also create your own custom phone number formats in Google Sheets.
How to Make a Custom Phone Number Format in Google Sheets
To choose your own way of formatting phone numbers, you’ll want to go to your format dropdown menu. Then, choose “custom number format.” Here’s a reminder of what that looks like.
Once you open that screen, you’ll see an option to choose common number formats. There’s also a spot at the top where you can enter your own phone number format. This is where you can add country codes or separate area codes in a unique way. Use the hash marks I discussed above to make it look how you want. Here’s what it looks like.
So there you have it. It’s pretty easy to make your own Google Sheets phone number format. In the “custom number formats” window, you can choose common layouts or create one that’s totally unique to your business. That’s the best way to ensure consistency across your financial documents. If you format all your numbers in one way, you’ll be less likely to make mistakes in a single spreadsheet.
Frequently Asked Questions
As I wrap things up, I wanted to make sure to answer some of the most common questions I see about phone number formatting in Google Sheets. If I missed any, please let me know in the comments below.
Is There a Phone Number Format in Google Sheets?
Yes, there’s an easy way to choose a phone number format in Google Sheets. The default style is US Style Domestic phone number format. You can recreate this by typing (“###”)”” “###”-“#### in the custom number formatting box. Check out my screenshots above for examples.
How Do You Add +1 to a Phone Number in Google Sheets?
There are two ways you can add the prefix +1 to your phone number. While the most obvious is to type it manually into a cell, that’s not going to be efficient for large sets of numbers. Instead, try the CONCATENATE formula. That automatically adds the +1 prefix to every phone number in your selection. And it’s super easy to do. Here’s how it works.
- Click on a cell in a new column next to the column that contains the phone numbers,
- Enter the formula
=CONCATENATE("+1 ", B3) .
The cell reference should be where your phone number is located.
- Click the tick for Suggested autofill or drag the formula down to apply it to all the rows that contain phone numbers.
This formula will add the “+1 ” prefix (including a space after the +1) to the phone number in cell A1 and concatenate them together, resulting in a phone number with the “+1” prefix. According to the FCC, this is important in California, Michigan, and in Illinois with the 708 area code.
Keep in mind that the resulting phone number data will be a text value, not a number, so you won’t be able to use it in calculations. If you want it to be a number, try adding the 1 without the + symbol. That’s the best way to avoid issues.
Is US country code 001 or 1?
The United States country code is 1. A bunch of people list it as 001 because it’s pretty common to have to dial 00 before international phone numbers in some regions. When you format phone numbers in Google Sheets, you can usually omit the two preceding zeros for US contact information.
How Do You Write a Valid US Phone Number?
Within the United States, phone number formats have three digit area codes, then three numbers, a dash, and four numbers. Standard formatting puts the area code in parenthesis, but many people now drop the parenthesis in favor of a dash. That means the ###-###-#### format is growing in popularity.
How Do I Fix an Incorrectly-Formatted Phone Number?
In Google Sheets, the easiest way to fix a formatting error is to remove formatting entirely. Then, highlight the phone number data and choose the “custom number format” from the dropdown menu. From there, you can choose the standard format. If you’re looking for screenshots, I cover this above in more detail.
How Do I Change the Phone Number Format in Google Sheets?
To change the Google Sheets phone number format, choose the formatting options menu. Then, click “number”. Then, from the dropdown menu, choose “custom number format.” That’s where you change the format for phone numbers. It’s also where you can create your own formats and where you change time formats!
How Do I Format a US Mobile Number?
US mobile numbers follow the same formatting conventions as landlines. There’s no way to differentiate a landline from a mobile number based on formatting.
Where Can I Find Phone Data for My Database?
The FCC provides APIs for developers. This includes the FCC Form 499 Filer Database, which you may find useful if you want to pull ID, FRN number, or other parameters.
Final Thoughts on Phone Number Formatting
In this article, I guided you through the Google Sheets phone number format. I showed you how to remove formatting errors, how to create a custom number format, and screenshots on where to find the formatting options menu. I hope you found this useful!
Other Google Sheets tutorials you may like:
