The process for applying Google Sheets phone number format is easy. You simply have to navigate to Format > Number > Custom number format and select the type of formatting you want.
In this tutorial, we will show you how to format phone numbers in Google Sheets and how to create your own custom phone number formats.
How To Format Phone Numbers In Google Sheets
In this tutorial, we’ll be adding 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.
The following steps show how to format phone numbers in Google Sheets with unformatted data. This example uses the United States domestic format: (123) 456-7890.
- 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.
See later in the guide to learn how to remove formatting from phone numbers if you received them in an incorrect format.
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.
You might be able to find the formatting you are looking for with the following examples. If not, they can provide a baseline you can modify into the format you are looking for.
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 Numbers in Google Sheets
The Google Sheets custom number formatting system can be quite overwhelming at first glance.
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.
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.
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.
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
There’s a simpler way to apply phone number format in Google Sheets using a single quote. Here’s how to do it:
- 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.
Your value will be formatted to the phone number format. Unfortunately, this method is not the best if you have a longer list of numbers because it can be quite tedious.
How to Remove Existing Formatting from Phone Numbers
Depending on how you received the phone number information in your spreadsheet, you may encounter cases of inconsistent formatting. Some numbers may have formatting, while 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.
The following steps explain how to remove formatting using the formula
=VALUE(REGEXREPLACE(*selected cell*,"[^[:digit:]]", ""))
which removes all non-digit values from a cell.
This example 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.
Frequently Asked Questions
Is There a Phone Number Format in Google Sheets?
No, there is no default phone number format in Google Sheets. But, you can apply a desired phone number format using the custom number formatting. For example, you can use the US Style Domestic phone number format by typing (“###”)”” “###”-“#### in the custom number formatting box.
How Do we Add +1 to a Phone Number in Google Sheets?
There are two ways you can add the prefix +1 to your phone number. The first way is by typing it int0 your cell. However, this method can be tedious.
Instead, you can use a formula that concatenates the “+1” prefix with the original phone number. Here’s how:
- 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.
Keep in mind that the resulting phone number will be a text value, not a number, so you won’t be able to use it in calculations.
Final Thoughts
In this article, we’ve guided you through the Google Sheets phone number format. We hope you found this tutorial useful!
Want to become a pro at using Google Sheets and Google Forms? We highly recommend this masterclass by SpreadSheet Point.
Other Google Sheets tutorials you may like:
Sumit is a Google Sheets and Microsoft Excel Expert. He provides spreadsheet training to corporates and has been awarded the prestigious Excel MVP award by Microsoft for his contributions in sharing his Excel knowledge and helping people.
