In this tutorial, you will learn how to format phone numbers in Google Sheets.
Google Sheets is a convenient tool for storing many different types of data, and phone numbers are no exception. Sometimes getting phone numbers to display correctly in Google Sheets can be tricky, but with proper storage and number formatting these issues can be resolved.
Correctly Storing Phone Numbers
In order to format phone numbers, the numbers need to be stored as digits only. This will also help with issues arising from the use of numbers entered with the leading plus sign used in the international phone number format. Google Sheets interprets a plus sign as the beginning of a formula and will then try to process the phone number as a calculation in errors or weird results
The steps to convert phone numbers to digits are below. Note that these steps permanently alter the data, so it’s a good idea to save a backup before continuing
Select the range containing the phone numbers and hit Ctrl + H on Windows or Cmd + H on Mac to bring up the Find And Replace dialog. Be sure to ONLY select cells containing phone numbers as all letters and special characters will be removed from these cells
Click the checkboxes beside Search Using Regular Expressions and Also Search Within Formulas. Note that Match Case will be automatically turned on
Regular expressions are a way of matching any string that meets certain criteria. In this case, any character that is not a number. The regular expression for this is [^0-9]. Enter this regular expression in the Find text field and leave the Replace With text field blank as we want to remove the characters by replacing them with nothing
Click on Replace All, and then Done
All phone numbers in the selection will be formatted as numbers only. They look ugly but in the next section we’ll format them
Once the phone numbers are digits only, they can be formatted using Number Formatting:
Select the phone number range and open the Format menu, hover over Number, then over More Formats at the bottom, and choose Custom Number Format to open the Custom Number Format dialog
Type in the phone number formatting rule you’d like to use then hit Apply. For example, to format a North American phone number with leading country code, you could type “+”#” “###” “###” “####
The following image shows the result of this formatting rule
Another example would be a UK phone number with 12 digits formatted as “+44 12 1234 5678”. The number format for that would be “+”##” “##” “####” “####.
The number format rules use the # character as placeholders for each digit of the phone number, with added characters such as leading plus, spaces and dashes wrapped in quote marks. Play around with the format rule till you get the ideal formatting
Example Spreadsheet: Make a copy of the example spreadsheet
In this tutorial, I covered how to format phone numbers in Google Sheets. Want more? Check out all the Google Sheets Tutorials.
Get more Google Sheets stuff
Subscribe to our mailing list and get interesting Google Sheets stuff and updates to your email inbox.
Thank you for subscribing.
Something went wrong.