Do you know that awkward situation when you copy and paste or manually enter unformatted phone numbers into Google Sheets? These numbers then appear as arithmetic operations, which is obviously not what you intended.
But fret not! We have the solution for you. In this article, we’ll present three simple methods to correctly format phone numbers in Google Sheets.
Unformatted Phone Numbers in Google Sheets
Let’s imagine that you have a list of eleven-digit phone numbers, including the country code and the regional code. When you add these unformatted phone numbers to your spreadsheet, whether through copy and paste or manual entry, you end up with the following result:
Well, that’s embarrassing! You could pre-format the cells as “Plain Text” by clicking on “Format”, then “Number”, then “Plain Text” before pasting the numbers. However, that will yield the following result:
None of these options properly format phone numbers in Google Sheets. So, what’s the solution? You need to remove the existing formatting and follow specific steps to correctly format phone numbers.
Read on, and we’ll introduce three easy methods to format phone numbers in Google Sheets.
Format Phone Numbers with a Simple Apostrophe
It turns out there are simple solutions to this problem, but you will have to manually edit each unformatted phone number for this first method.
All you need to do is add an apostrophe before the number. For example, if the phone number is “+1-555-675-8098”, then you should type “‘+1-555-675-8098” in Google Sheets to get the desired format:
The simple apostrophe tells Google Sheets not to treat the number for calculation. Once you press Enter, Google will insert the character string and format the phone numbers like this:
This method even works if you copy and paste the number into a document. Convenient, isn’t it?
Format Phone Numbers with a Formula
You can achieve the same result by formatting phone numbers using the following formula: ="<phone number goes here>". For the previously mentioned phone number, you would enter it into the spreadsheet like this: ="+1-555-675-8098". You’ll get the same result – a properly formatted list of phone numbers in Google Sheets.
Format Phone Numbers with Custom Formatting
Simultaneously formatting phone numbers can be very helpful if you plan to manually add multiple phone numbers one by one from a paper document to your Google Sheets spreadsheet. In this case, you can pre-format the cells by adding a custom formatting.
Step 1: Click on “Format” in the menu, then “Number” in the drop-down menu, scroll to the bottom and click on “More Formats”, then “Custom number formatting” in the menu.
Step 2: Add one of the following formats for phone numbers (these are for phone numbers in the United States) and click “Apply”:
+"#"-"###"-"###"-"####will give you +1-555-9069-665###"-"###"-"####will give you 555-906-9665(###)" "###"-"####will give you (555) 906-9665(###)" "###" "####will give you (555) 906 9665
Step 3: Enter your phone numbers in Google Sheets (without the special symbols), and Google Sheets will automatically convert the phone numbers into the appropriate format. This method will save you time when entering them one by one!
Conclusion: Properly Formatted Phone Numbers
As we have demonstrated above, some solutions may seem like common sense but don’t actually format phone numbers correctly in Google Sheets. However, this Google Sheets tutorial has presented three methods that result in cells with properly formatted phone numbers. Whenever you encounter the issue of formatting phone numbers in Google Sheets in the future, remember our three easy solutions: apostrophes, the format formula, or custom formatting.
And if you want to further enhance your spreadsheets, head over to Crawlan.com to discover how to optimize your data and make it even more powerful.
Supercharge your spreadsheets with Crawlan!
