Skip to Content

How to Format Phone Numbers in Google Sheets (2 Easy Methods)

Have you ever entered phone numbers into Google Sheets only to be frustrated when they lose their formatting? Despite Sheets being an incredibly useful tool, phone numbers often default to long strings of numbers with no sensible structure. If you’ve ever wondered, “How do I format a phone number in Google Sheets?” you’re not alone. Correctly formatting phone numbers in Google Sheets can be surprisingly tricky for beginners.

That’s why, in this guide, you’ll learn everything there is to know about how to format phone numbers in Google Sheets. We’ll cover why phone number formats get messed up in Sheets, to begin with, and the step-by-step process for fixing the formatting. With a few simple tweaks, you’ll be able to insert phone numbers into Google Sheets and maintain easy-to-read and uniform formatting across all cells.

After reading today’s guide, you’ll be a pro at knowing how to format phone numbers in Google Sheets. No longer will you have to deal with frustrating, overly long digit strings when adding phone numbers.

But before we delve further, let’s quickly see some phone number formats you should know. 

Types of Phone Number Formatting

There are many potential ways to format phone numbers, especially when considering international dialing codes. Here are some of the most common phone number formats used in the United States:

Domestic US Style: (123) 123-1234

This standard U.S. phone number format starts with the 3-digit area code in parentheses, followed by a space, and then the 3-digit prefix and 4-digit line number, separated by a hyphen.

Domestic US Style with dashes: 123-123-1234

A common variant without parentheses but still containing the area code and number groups separated by hyphens.

International US Style: +1-123-123-1234

For international dialing, this adds the +1 country code for the U.S., followed by hyphens separating the rest of the number.

Letterhead Style US: 123.123.1234

Some document formats, like letterheads, display numbers using dot separators instead of spaces or dashes.

Germany Dialed: 001-123-123-1234

When dialing from Germany to the U.S., 001 is the exit code, followed by the standard US phone format.

France Dialed: 191 123 123 1234

For France, 19 is the exit code before dialing a U.S. number formatted with spaces instead of symbols.

Ways to Format Phone Numbers in Google Sheets

When formatting phone numbers in Google Sheets, you have two main options: using an apostrophe before the number to force a format or creating a custom number format.

The apostrophe method is quick and easy, allowing you to insert an apostrophe before pasting or typing a phone number in a cell to format it automatically. However, this only works for the phone number you’re directly typing in. The custom number format approach takes a bit more work upfront but allows you to apply your chosen phone number format evenly across all cells in the spreadsheet or range.

In the next section, we will show you how to format phone numbers in Google Sheets using any of these methods. 

We will show you how to format phone numbers using an apostrophe. After that, we will take things up a notch by showing you the second method. 

Formatting Phone Numbers in Google Sheets Using Apostrophe

If you just need to format a phone number or two in Google Sheets quickly, the easiest method is to use an apostrophe.

For this example, let’s say we have an unformatted phone number in cell A1 like this:

A1: 2126224311

Our goal here is to format the phone number to the common US style you’re accustomed to. We want the phone number to show like this: +(212) 622-4311. 

Here is how to go about it:

Step 1: Click on Cell A1

You first want to click on cell A, where you have the unformatted phone number. 

Step 2: Update the Phone Number Format

Having selected the cell with the unformatted phone number, you want to now update the phone number to your desired format. For this example, we will update our format to +1(212) 622-4311. However, you want to ensure you don’t press Enter just yet. 

Step 3: Add the Apostrophe to the Phone Number

Having updated your phone number to the US format you want, the next thing you want to do is add the apostrophe to the phone number. 

It should look something like this: ‘+1(212) 622-4311

Step 4: Press Enter

After adding the apostrophe symbol to the phone number, as we showed you in the previous step, all you need to do is press the Enter button on your keyboard. Google Sheets will automatically update the phone number to your preferred format without the apostrophe added. 

Here is what ours looks like: 

Now, that’s how to format phone numbers in Google Sheets using the apostrophe method. 

If we didn’t add the apostrophe to the phone number, we would have gotten an error message when we hit the enter button on our keyboard. 

Here is what it would have looked like: 

What Can Go Wrong With This Method?

When adding formatting characters like dashes or parentheses into the phone number, they can sometimes have unintended effects in Google Sheets.

For example, if you typed +1 (212) 622-4311 without the apostrophe, Sheets would interpret the plus and minus signs as mathematical operators rather than formatting symbols. This could produce an incorrect output number or an error message, which we got in our case. 

The apostrophe tells Sheets to treat everything that follows as plain text for formatting rather than values to compute. But it’s easy to forget the apostrophe, which can mess up the formatting.

Additionally, this apostrophe method only works on individual phone number cells. It would be extremely tedious to apply it across an entire list of phone numbers in a sheet.

So, while useful for one-off phone number formatting, the apostrophe trick has some major limitations. For widespread phone number formatting, the Custom Number Format option covered next is much more effective and resilient. And we will show you how to use that in the next section. 

Formatting Phone Numbers Using Custom Number Formats

When you need to apply consistent phone number formatting across entire columns or sheets of numbers, a custom number format is the best approach.

Let’s look at how to create a reusable phone number format in Google Sheets using the following sample data:

With our sample data nicely put together, let’s quickly show you the steps to format phone numbers in Google Sheets using custom number formats. 

Here is how to go about it: 

Step 1: Select the Column with the Phone Number

To start, click on the top cell in the column with the raw phone numbers you want to format. In our example, this is column B with John Smith’s number.

Next, hold down the left mouse button and drag down through all the cells in column B to select the entire column with unformatted numbers. You’ll see a thick blue border around the column to indicate all numbers are selected.

Selecting the column allows us to apply the custom phone format evenly to all numbers simultaneously.

If you only have one cell to format, you can select that individual cell instead.

Column B should now be fully selected with the border highlighted. 

Step 2: Format > Number > Custom Number Format

Navigate to the Format menu after highlighting the unformatted numbers, as we showed you in the previous step. This will reveal a drop-down menu with several options. From those options, you want to select the option for Number. 

Clicking on the Number option will launch another sub-menu with several options. Here, you want to scroll all the way down and select the option for Custom number format. 

Step 3: Enter Custom Date Format

After selecting the option for Custom number format, a pop-up window should launch in the middle of your screen. 

In the “Custom number format” text box that opens, type the formatting you want applied. For our example, we will go with:

(###) ###-####

This will format the phone numbers with parentheses around the 3-digit area code, then a space and dash before the 4-digit line number segment.

While you can copy our format above, you can also create your own variations as well. Here are some popular variations you can explore:

  • (###)###-####
  • ###-###-####
  • ###.###.####

Be sure to incorporate the # symbols as placeholders for the digit segments in proper order.

Once the format is structured, click the Apply button to apply it to the selected phone number cells.

If you did that correctly, the phone numbers should be instantly formatted to reflect your preferred formatting. 

Here is what ours looks like: 

Now, that’s how seamless it is to format phone numbers in Google Sheets using custom number formats. It’s pretty easy and straightforward. 

Final Thoughts

As we’ve seen, formatting phone numbers in Google Sheets can be accomplished using either the apostrophe shortcut or custom number formats:

The Apostrophe Method:

  • Useful for one-off phone number formatting
  • Just add an apostrophe before typing out the formatted number
  • Easy but doesn’t scale across sheets

Custom Number Formats:

  • Create once, reuse often for uniform formatting
  • Applies set format consistently across cells
  • More work initially but saves effort long-term

The apostrophe trick works in a pinch for a few phone numbers. But for robust sheets with large phone number datasets, custom formats are vastly superior in consistency, efficiency, and simplicity.

The custom number format approach may take a few more clicks initially but prevents you from manually structuring each cell. And you can reuse formats across all your Google Sheet projects.

So, in summary, use the apostrophe for quick, individual cell formatting. But depend on custom formats for reliable and widespread phone number structuring in Google Sheets.

With these methods mastered, you’ll be able to format phone numbers in Google Sheets without stressing.