In this article:

How to Format Phone Number in Google Sheets

May 8, 2024

Unformatted Phone Numbers in Google Sheets

Let’s say we have a list of eleven-digit phone numbers that include the country code and the area code. When we add unformatted phone numbers to our spreadsheet, whether by copy-pasting or by manually typing the phone numbers, we get the following:

Phone numbers converted to arithmetic operation by Google Sheets
Phone numbers converted to arithmetic operation by Google Sheets

Well, that's awkward. You could pre-format the cells into Plain text by clicking Format, then Number, then Plain text before pasting the numbers,

Converting the cell to plain text

but that will result in the following:

An equal sign appended to the start of the phone number

You can try typing phone numbers manually, but Google Sheets will format phone numbers like this:

Google Sheets still calculated the phone number as arithmetic operation even in plain number

None of these options will properly format phone numbers in Google Sheets. So, what is the solution? We have to remove existing formatting and follow specific steps to format phone numbers correctly.

Read on below, and we will take you through 3 easy methods to format phone numbers correctly in Google Sheets.

Format Phone Numbers with A Single Quote

There are, it turns out, simple workarounds to the problem, but you will have to manually edit unformatted phone numbers one-by-one for this first solution.

Simply add an apostrophe before the number. For example, if the phone number is +1-555-675-8098, then when you type it in Google Sheets, you should type ‘+1-555-675-8098 to receive the desired format:

Phone number with a single quote appended before it

The single quote will tell Google Sheets that it is not supposed to process the number for calculation. Once you press Enter, Google will insert the string and format phone numbers as follows:

Phone number displayed as is
This will work even if you copy the number and paste it to, let’s say, a document, Neat, right?  

Format Phone Numbers with A Formula

You achieve the same result if you format phone numbers in this way: =”<phone number="" goes="" here="">”.</phone> For the phone number above, we write it in the spreadsheet as =”+1-555-675-8098”. You will receive the same result, a list of phone numbers in Google Sheets formatted correctly.

Format Phone Numbers with Custom Number Format

Formatting phone numbers simultaneously can be very helpful if you plan to manually add lots of phone numbers one-by-one from paper into your Google Sheets. In this case, you can pre-format the cells by adding a custom number format. 

Step 1: Click Format in the menu, then click Number in the drop-down menu that would appear, and then go to the end and click More formats, and then click Custom number format from the format menu.

Click Format, then Number, then More formats, then Custom number format highlighted

Step 2: Add any of the following phone number formats (these are for phone numbers in the United States) and click Apply:

  • "+"#"-"###"-"###"-"#### will give +1-555-9069-665
  • ###"-"###"-"#### will give 555-906-9665
  • (###)" "###"-"#### will give (555) 906-9665
  • (###)" "###" "#### will give (555) 906 9665

Step 3: Type your phone numbers in Google Sheets (but without the special symbols), and Google Sheets will convert phone numbers automatically to the proper format. This method will make your one-by-one input much faster!

Conclusion: Phone numbers formatted correctly

As we have shown above, some seemingly common sense solutions fail to properly format phone number in Google Sheets. However, this Google Sheets tutorials has demonstrated three methods that result in cells with the proper phone number format. Whenever you are faced with this phone number format problem in Google Sheets in the future, remember our three easy solutions: quotes, a format formula, or custom formatting.

Supercharge Your Spreadsheets with Lido

🚀 Import data from anywhere and build custom tools and powerful dashboards straight from your spreadsheet. Discover what's possible in our Build Gallery.

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.
Get your copy of our free Google Sheets automation guide!
  • 27 pages of Google Sheets tips and tricks to save time
  • Covers pivot tables and other advanced topics
  • 100% free

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->