In this article:

How to Format Phone Number in Google Sheets

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.


Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
ADDCALENDAREVENT
Creates a one- or multiple-day calendar event with optional attendees
ADDHUBSPOT
Adds an object to Hubspot
ADDSLACKCHANNEL
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
CREATEGOOGLEDOC
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL
Sends an email using your Google account
SENDOUTLOOK
Sends an email using your Microsoft Outlook account
SENDSLACK
Sends a Slack message
SENDSMS
Sends an SMS message using your Twilio account
UPDATECELL
Updates cells with given values
UPDATEHUBSPOT
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started