September 15, 2020

How to Format Phone Number Data on Google Sheets

Google Sheet spreadsheet
SECTIONS
  1. Single Quote Method
  2. Formula Method
  3. Custom Number Format Method

Let’s say we have a list of eleven-digit phone numbers that include the international code and the area code. When we add them, as is, to our spreadsheet, whether by copy-pasting or by manually typing the number, what we got are the following:

Phone numbers converted to arithmetic operation by Google Sheets

Well that is 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

And you will get this:

An equal sign appended to the start of the phone number


You can try typing it manually, but you will get this:

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

So, what is the solution? We'll take you through 3 easy methods to get your phone numbers formatted correctly on Sheets.

Format Phone Numbers with A Single Quote

There are, it turns out, simple workarounds to the problem. But you will have to manually add them one-by-one (which is the more likely case): simply add a single quote before the number. For example, if the phone number is +1-555-675-8098 then when you type it in Google Sheets it should be ‘+1-555-675-8098:


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 it will look like this:


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 can do the same thing if you format the phone number in this way: =”<phone number="" goes="" here="">”.</phone> So for the number above, we write it to the spreadsheet as =”+1-555-675-8098”. You would get the same result.


Format Phone Numbers with Custom Number Format

If you would be typing lots of numbers one-by-one from paper to the spreadsheet, 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.

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

  • "+"#"-"###"-"###"-"#### 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 (but without the special symbols). This method will make your one-by-one input much faster!

Suscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.