Format Phone numbers in Google Sheets

Formatting phone numbers in Google Sheets refers to how they would appear in the cells. Unformatted phone numbers wouldn’t look any different than just some digits written together, without any dashes, slashes or spaces to indicate the digit distribution in a phone number.

You could input phone numbers by putting the dashes in between the digits yourself but Sheets will interpret it as an arithmetic operation and perform subtraction automatically.

Thankfully we can make use of custom number formatting to specify how to deal with numbers in our worksheet and Sheets will comply.

Formatting with single quote

A simple way to get rid of the phone number formatting problem is to just type a single quote (‘) at the beginning of the phone number and then type the rest of the number with desired formatting with dashes etc.

This single quote will tell Google Sheets that it is not supposed to process the numbers for any kind of calculations and return as is. Once you press enter after typing everything, the quote will not appear in the cell, only the phone number will.

Although this is quite simple and a quick method, it can get tedious having to do this for every cell and phone number in your worksheet if there are many. Hence we have better methods.

Using Custom Number Formatting

If you need to enter a lot of phone numbers in your spreadsheet, using the custom number formatting feature would prove to be a lot quicker and easier in the long run. This feature lets you choose a specific way to display the numbers in a selected range of cells.

To begin, select the row/column where you want to enter phone numbers.

Select Format> Number> More Formats> Custom number format.

In the dialogue box that appears, enter a suitable number formatting formula and press Apply.

The selected row/column will now have this number formatting style applied and all inputs would be displayed likewise.

Formatting Formulae

There are different ways to write phone numbers based on your geographical position, culture or even personal preference. You can develop your own formula for any format using these building blocks:

  • #– This symbol is used to represent the digits in the numbers. The first “#” represents the first digit, the second one represents the second digit and so on. So on a typical 10 digit phone number format, there will be 10 “#”.
  • “”– Double quotes are used to specify the location and type of delimiter used for the format. Just put the delimiter between the quotes.

For example, the phone number 03326807475 can be represented as (033)2680 7475 and the format formula would be “(”###“)”####“ ”####.

Mix and match the symbol to come up with a formula specific to the phone number format you want to use in your spreadsheet. That will be the end of this article and I hope you learnt something new today.

Recent Content