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.

Phone number datasets
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.Select the phone numbers that you want to format
Open the “Format” drop menu, choose “Number,” select “More Formats”, and pick “Custom number format.”Go to more formats in custom number formatting
Enter the format formula in the “Custom number formats” field and select “Apply.” In the example, we’re using the formula “(“###”)”” “###”-“####Enter the custom format in the dialog box
The column will now format phone numbers accordingly.Formatted phone numbers
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.

admin January 3, 2022

0 216 5 minutes read

A WordPress Commenter
Hi, this is a comment. To get started with moderating, editi...
Georgia Waltrip
Hi, this is a comment. To get started with moderating, editi...
Donald Allbright
Hi, this is a comment. To get started with moderating, editi...
Ivy Torres
Hi, this is a comment. To get started with moderating, editi...
Donald Allbright
Hi, this is a comment. To get started with moderating, editi...

How To Format Phone Numbers In Google Sheets

admin

Leave a Reply Cancel reply

A Refined Seattle Restaurant, Hold the Table Linens

Bosch looking to smart devices to get ahead in the cloud

Nintendo Details Next Miitomo Update

admin

Subscribe to our mailing list to get the new updates!

How to Compare Two Columns in Google Sheets

How to Convert Text to Numbers In Google Sheets

Related Articles

Controlling PWM fans with the Raspberry Pi CM4 IO Board’s EMC2301

Mini-ITX Seaberry adds 11 PCIe slots to a Raspberry Pi

Turing Pi 2: 4 Raspberry Pi nodes on a mini ITX board

Install Python 3.9 on Raspberry Pi OS or Debian 10 (for Ansible or other uses)

Leave a Reply Cancel reply

A Refined Seattle Restaurant, Hold the Table Linens

Bosch looking to smart devices to get ahead in the cloud

Nintendo Details Next Miitomo Update