Google Sheets Phone Number Format: Easy Methods [2024]

By

Adding phone numbers into Google Sheets — without formatting — can quickly become overwhelming. Thankfully, there’s an easy way to customize your Google Sheets phone number format.

The quickest way is to click select the following in the top menu bar of your Google Sheet: “Format” > “Number > “Custom Number Format.”

In this guide, I’ll show you a few different ways to format a phone number in Google Sheets. I’ve included detailed step-by-step guides (with screenshots) to help you get the hang of things. Let’s get to it!

Google Sheets Phone Number Format Example

Before we begin, let’s look at a phone number when formatted and unformatted. I mean unformatted numbers that appear only as digits (without any spaces or symbols).

Unformatted vs formatted phone numbers

The difference between unformatted and formatted is easy to see. In the above screenshot, the formatted number follows the standard US domestic format.

1. Easiest Method: Phone Number Formatting in Google Sheets Using the Format Menu

If you want Google Sheets to format a phone number automatically, one of the easiest ways is with the “Format” option (in the top menu bar of the app).

  1.  Highlight the column you want to format.
    • In the example below, I highlighted column B.
Column B cells highlighted in Google Sheets
  1. Select “Format” > “Number” > “Custom Number Format.”
Google Sheets format menu, custom number format
  1. For this example, I want to use the format (###) ###-#### from the screenshot below.
    • Enter the format formula in the “Custom number formats” field and select “Apply.”
Google Sheets phone number format—Example custom number format formula
  1. The numbers should now be formatted correctly in column B.
Formatted phone numbers in Google Sheets

Writing Your Phone Number Formula

It may seem overwhelming, but creating a Google Sheets custom number format phone number is simpler than you think. You can add:

  • Symbols like plus signs, dashes, dots, and parentheses
  • Spaces to create separation
  • Predetermined numbers (e.g., country code, area code)

The following chart includes some symbols you can use in your Google Sheets phone number formula.

Note: Quotation marks (“ “) are not included in formulas and are used for demonstration purposes only.

Symbol Represents
# A digit in the phone number
“ “ Add a space
( ) Add parentheses
Add dash
. Add period
123 Add specific predetermined numbers
+1- Add the US international code followed by a dash

With that in mind, you can break down any phone number format into its basic formula. The US domestic standard is (555) 123-1010. Let’s convert it:

  1. Figure out which symbols you need and whether there are spaces in the format. As you can see, we need an opening and closing parenthesis, a space, and a dash.
Symbols needed in google sheets phone number format
  1. Use parentheses and # symbols as well as a space to create the first section (###).
Step 1 using symbols to create google sheets phone number format
  1. Add your digits with # symbols and a dash ###-####.
Step 2 using symbols to create google sheets phone number format

Once you know how to convert each formula component, use conditional formatting to organize your spreadsheet better!

Using An Existing Format

You can also use an existing phone number format, such as the examples below. You may copy their formulas into your “Custom Number Format” settings in Google Sheets.

Style Formula Example
US Domestic (###) ###-#### (555) 123-1010
US Domestic with dashes ###-###-#### 555-123-1010
US Style International +1-###-###-#### +1-555-123-1010
US Letterhead Style ###.###.#### 555.123.1010
Dialed from Germany 001-###-###-#### 001-555-123-1010
Dialed from France 191 ### ### #### 191 555 123 1010

2. Phone Number Format Using a Single Quote

Are you working with a small data set and want the quickest way to format it as a phone number in Google Sheets?

You can use something as simple as a single quotation mark ( ‘ ). This symbol tells Google Sheets that you want to use your specific formatting.

Here’s how:

  1. Select a cell containing a phone number.
Google Sheets cell with phone number
  1. Format your number however you prefer.
    • In the example below, I used the US domestic standard format.
Google sheets cell with formatted phone number
  1. Type an apostrophe (‘) before the phone number and press “Enter.”
Apostrophe before formatted phone number in a cell

And you’re done! You can repeat this process as much as you’d like, but it’s best for small datasets where the manual effort isn’t too intensive.

How To Remove Existing Formatting from Phone Numbers

Sometimes, data needs to be cleaner, more organized, and simpler to read. Therefore, you may need to remove formatting from existing data.

You don’t have to regather your data or manually reformat everything — instead, use the Google Sheets’ VALUE and REGEXREPLACE functions. Let me show you how.

  1. Create a new column next to your preformatted numbers.
    • If there is no space, right-click on the preformatted number column and select “Insert column right.”
Context menu for inserting a column to the right in Google Sheets
  1. Name it whatever you like, but in this example, I used “Helper Column.”
Preformatted column and helper column
  1. Select the first cell in the helper column and insert the following formula.
    • Make sure you replace “CELL” with your reference cell (as in, the cell with the number you want to unformat).
    • In this example, I used cell A2.
=VALUE(REGEXREPLACE(CELL,"[^[:digit:]]", ""))
Example of a cell with the VALUE and REGEXREPLACE formula
  1. Press “Enter.”
    • You can also accept “Suggested autofill” to have Google Sheets do the remaining work.
Cell autofill dialog
  1. Select all the unformatted phone numbers in the helper column and select “Copy.”
    • You can use CTRL +C, CMD + C, or your right-click menu.
Right-click context menu, copy highlighted
  1. Highlight all the formatted phone numbers in the original column, then right-click and select “Paste special” > “Values only.”
    • You can also use the keyboard shortcut CTRL + SHIFT + V for Windows and Cmnd + SHIFT + V for Mac.
    • You may now delete the helper column.
Right click context menu, paste special, values only

You now have a clean slate with unformatted phone numbers!

How To Add a Prefix to a Phone Number Using CONCATENATE Formula

Once you know how to format a phone number in Google Sheets, you can automatically add a prefix to each.

The most efficient way to add country/local codes is by using the CONCATENATE formula. Here’s how to add a country code to a phone number in Google Sheets:

  1. Select a cell in a new column next to the phone number you’re adding a prefix.
    • In my example, the newly selected cell is C2.
    • B2 is the cell containing the phone number.
A selected cell in Google Sheets
  1. Enter the formula:
=CONCATENATE("+1 ", B2) .
The CONCATENATE formula in a cell in Google Sheets
  1. Press “Enter.”
    • If you get a prompt for autofill, click on the green checkmark to agree.
    • Otherwise, click on the bottom right corner of cell C2 and drag it downwards.
    • Release your mouse key to apply the formula to the other phone numbers automatically.
Clicking and dragging a cell to automatically apply a formula in Google Sheets GIF

This formula will add the “+1” prefix (with a space) to the phone number in cell B2. It will concatenate (combine) them together, resulting in a phone number with the “+1” prefix.

According to the FCC, this is important in California, Michigan, and Illinois 708 area codes. The resulting phone number data will be a text value, not a number. This means that you won’t be able to use it in calculations.

If you want a number rather than text, add 1 without the + symbol.

Related Reading: How to Convert Text to Date in Google Sheets

How To Remove Country Code from a Phone Number in Google Sheets

You know how to add a country code, but what if you want to remove it from your dataset? Thankfully, Google Sheets has a function that can help you do this easily.

  1. Select a cell next to the first phone number you want to remove the country code from.
    • In the example below, that’s cell B2.
Country code and no country code columns
  1. Enter the following formula:
=RIGHT(CELL,LEN(CELL)-3)
    • This will allow you to remove the first three characters of the phone number (+1-).
    • Replace “CELL” with your reference cell.
    • In the example below, I’ve chosen A2. Therefore, the correct formula is
=RIGHT(A2,LEN(A2)-3)
Cell with formula for removing a country code from a phone number

Note: You can shorten the number as much as you’d like. Change the “3” to any number of characters you want to subtract from the beginning of the phone number.

For example, if you want to show only the last four digits of the phone number, your formula would be:

=RIGHT(A2,LEN(A2)-11)
Example of formula to shorten a phone number by 11 characters

This would return the result:

Result of formula to shorten a phone number by 11 characters
  1. Press “Enter” then accept autofill so Google Sheets does the work for you.
    • You can also click and drag on the bottom right corner of the first cell to auto-populate the remaining cells.
Formula suggested autofill dialog in Google Sheets
  1. You have successfully removed the country code from your phone numbers!
Country code and no country code columns

Frequently Asked Questions

Is There a Phone Number Format in Google Sheets?

If you’ve tried typing a phone number into Google Sheets, you might have noticed that they typically default to a long string of unformatted numbers.

Luckily, you can create a custom phone number to help you automatically manage your data. The steps in the guide above can help you determine your proper format.

How Do You Add +1 to a Phone Number in Google Sheets?

The easiest way to manually add “+1” to a phone number is simply by typing it into the cell. However, this method is impossible for large datasets.

Therefore, using the CONCATENATE formula is the most efficient way to add the +1 prefix to every phone number. Follow the steps in the guide above to use this formula.

Is the US country code 001 or +1?

The US country code is 1. When calling the US from another country, dial 001 or +1 (which indicates a foreign number). You can omit the 00 for US contact info when formatting phone numbers in Google Sheets.

How Do You Write a Valid US Phone Number?

Within the United States, phone number formats have three-digit area codes followed by three numbers, a dash, and four numbers: (###)###-####.

Standard formatting puts area codes in parentheses, but many people drop the parentheses in favor of dashes: ###-###-####.

How Do I Fix an Incorrectly-Formatted Phone Number?

The simplest way to fix a formatting error is to clear the formatting entirely. Don’t forget to convert cell text to numbers if necessary.

Next, highlight the phone number data and choose the “custom number format” from the dropdown menu. You can choose the standard format, as shown in the tutorials above.

How Do I Change the Phone Number Format in Google Sheets?

Changing your Google Sheets telephone number format is simple. Click on “Format” on the top menu bar.

Then, from the dropdown menu, choose “Custom number format.” Next, choose how you’d like your phone number to appear. You can find the detailed steps in the guides above.

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.

Conclusion

The beauty of Google Sheets is the flexibility and abundance of customization options. I hope this guide helped you learn how to configure a Google Sheets phone number format as quickly as possible!

To upskill all things related to Google Sheets, check out this Google Sheets Masterclass that covers automating tasks and saving you time and money!

Want to confirm you have the correct phone numbers? Find the owner of a cell phone number for free!

Related:

Fact Checked By Cindy Wong

Popular Posts