Google Sheets Phone Number Format: Easy Methods [2024]

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).

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.

2. Select “Format” > “Number” > “Custom Number Format.”

3. 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.”

4. The numbers should now be formatted correctly in column B.

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.

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.

2. Use parentheses and # symbols as well as a space to create the first section (###).

3. Add your digits with # symbols and a dash ###-####.

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.

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.

2. Format your number however you prefer.
   • In the example below, I used the US domestic standard format.

3. Type an apostrophe (‘) before the phone number and press “Enter.”

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.”

2. Name it whatever you like, but in this example, I used “Helper Column.”

3. 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:]]", ""))

4. Press “Enter.”
   • You can also accept “Suggested autofill” to have Google Sheets do the remaining work.

5. 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.

6. 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.

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.

2. Enter the formula:

=CONCATENATE("+1 ", B2) .

3. 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.

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.

2. 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)

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)

This would return the result:

3. 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.

4. You have successfully removed the country code from your phone numbers!

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:

• Conditional Formatting Based on Another Cell Value in Google Sheets
• How To Convert Military Time in Google Sheets
• How To Combine Formula and Text in Google Sheets