With a little know-how and effort, you can format phone numbers in Google Sheets any way you’d like them to appear.
Unfortunately, Google Sheets doesn’t have built-in formatting presets for phone numbers.
However, you can easily get this done using some know-how for number formatting a little bit of formula magic.
The process for applying the formatting is easy; the tricky part is determining the right format/formula.
In this tutorial, I will show you how to format phone numbers in Google Sheets and how to create your own custom phone number formats.
This Article Covers:
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 under 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.
Example Formatting Formulas
- 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 straight-forward 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.
How to Remove Existing Formatting from Phone Numbers
Depending on how 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 right.”
- Name the column as Header 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.”
- 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.
I hope you found this tutorial useful!
Other Google Sheets tutorials you may like:
- Format Painter in Google Sheets (Copy formatting easily)
- Conditional Formatting Based on Another Cell Value in Google Sheets
- How to Convert Text to Numbers In Google Sheets
- How to Convert Military Time in Google Sheets?
- How to Combine Formula and Text in Google Sheets
Sumit
Google Sheets and Microsoft Excel Expert.
