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:
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,
but that will result in the following:
You can try typing phone numbers manually, but Google Sheets will format phone numbers like this:
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.
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:
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:
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.
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.
Step 2: Add any of the following phone number formats (these are for phone numbers in the United States) and click Apply:
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!
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.
