How to Format Phone Numbers in Google Sheets (In 4 Steps)

I am going to discuss formatting phone numbers in Google Sheets and how you can do that easily. For more topics on Google Sheets and learning about all kinds of tips on working on it, you can check out other tutorials provided by TeqTog!

So, here we go!

Format Phone Numbers in Google Sheets

To show you how you can format phone numbers in Google Sheets I will give you an example with such data.

Example data of a list of customer names given and their phone numbers

As you can see above, there is a list of customer names given and their phone numbers. Now, the phone numbers are in their unformatted version, and here is how you can have them formatted.

1. The first that you have to do is select the range of cells in which you contain the phone numbers. Then you have to apply the format feature to this data. Go to the menu bar on the top and find the “Format” option and click on it. Then, from the drop-down menu, select the “Number” option.

Select Format option then Number option then Custom Number Format option

2. At the very end of the drop-down menu, you can see the option, “Custom Number Format”. Select this option.

“Custom Number Format” may not always be available on the second drop-down menu. You may find the option “More Formats” at the very end of the second drop-down menu and, by hovering your cursor on top of this option, a third drop-down menu will appear and from there, you can find the feature “Custom Number Format”

3. A dialogue box will appear in which you can give the input for your preferred phone number format. These are some of the formats which you can apply:

  • + ###-###-#### This will give you “+102-304-5005 In the same way, you can set the format in other arrangements as well; such as
  • ###-###-#### Obviously, this will give you the arrangement “102-304-5005”.
  • You can also add parenthesis to the formats. (###) ###-#### This will make the phone number appear as, “(102) 304-5005”.
  • Moreover, (###) ### #### This will be “(102) 304 5005”.

You can add dashes and the parenthesis into the formats as you like and then click on “Apply” after you have given your preferred style.

give your preferred style in Custom Number Formats pop up box

4. Once applied, the phone numbers will change to the formats that I want and appear as so in my data.

How to Format Phone Numbers in Google Sheets

Templates for Phone Numbers

There are a lot of options when it comes to a format for phone numbers. You may have a hard time deciding which format to select; so unless you have a predetermined format in mind, here is a list of samples for other kinds of formats available for phone numbers:

CountryStyleFormat
USADomestic Dashes ###-###-####
USAInternational+1-###-###-####
USALetterhead###.###.####
USADomestic(###) ###-####

Converting the Phone Numbers into Digits

Although I have already shown you how you can format a phone number, it is important that you had first converted the phone numbers into digits only. If you need to do this, there are just a few simple steps to get it done:

  • Start with selecting the range of data that has the phone. Make sure that you are only selecting the phone numbers.
  • Open the Find and Replace dialogue box.
  • From the options given in the dialogue box you need to select the following features:
  • Match case
  • Search using regular expression
  • Also, search within formulas
  • In the “search using regular expression” field, give the input “[^0-9]”. What this means is, the Search and Replace will identify all of them, and, any data that is not a numerical value. For the Replace field, leave it empty. By doing so, all non-numerical characters will be identified and replaced with nothing; hence, removed.

Now, you have a list of phone numbers that are purely digits and now they can be formatted as you want.

Final Thoughts

To summarize, all you need to do is, make sure all of the phone numbers selected are actually pure numerical values and then, format them accordingly.

I hope my tutorial on how you can format phone numbers in Google Sheets has helped you.

Best of luck!

Read More Google Sheet Tips On:

How To Export As A CSV File From Google Sheets (Easy One Minute Tutorial)

How to Count Unique Values in Google Sheets (2 Easy Methods)

Does Not Equal in Google Sheets (Symbol <>)

How to Freeze Rows in Google Sheets (Quick & Easy Shortcut Methods)

Was this article helpful?
YesNo


Related More Articles

Leave a Comment