Uncategorized

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.

Phone numbers with dashes
Create a helper column next to the preformatted numbers by right-clicking on the column and selecting “Insert 1 right.”Insert one column to the right
Name the column as Header columnHelper 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.Formula to remove phone number formatting
Drag the formula down the column. This will populate the rest of the column with unformatted phone numbers.Drag the formula to all columns
Highlight the phone numbers from the helper column and select “Copy” from the right-click menu.Copy the phone numbers
Choose the original column phone number values, right-click, select “Paste special,” and “Paste values only.”Paste as values
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.
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital
garetdigital

Related Articles

Leave a Reply

Back to top button