How to Format Phone Numbers in Google Sheets (2 Easy Ways)

Phone numbers usually start with the “+” symbol or “0”, and  Google Sheets interprets it as a formula or mathematical calculation. So, when we deal with a dataset in Google Sheets, including phone numbers, we often need to format that. We can easily do the formatting using ‘ (apostrophe) and the Find and Replace option, or using the Custom number format.

In this article, we will explain both of these options, using ‘ (apostrophe) with the Find and Replace option and the Custom number format to format phone numbers in Google Sheets.

We will use the following dataset to format the phone numbers using the ‘ (apostrophe) and the Find and Replace option.

In this dataset, the original format of the phone numbers is like this: +X-XXX-XXX-XXXX (such as +1-531-176-4538). However, Google Sheets treats them as formulas and so they appear as numbers, such as -5244. Thus, we need to format them and restore the original appearance.

Steps:

➤ Select the entire column D.
➤ Now, type  Ctrl  +  H  or select Edit from the top toolbar. A new window will be opened.
➤ Select the “Find and replace” option from that window.

➤ A pop-up will be opened. In the Find box, write =.
➤ Now, in the Replace with box, write ‘ (apostrophe).
➤ Then, select Also search within formulas, and finally click the Replace all option.

➤ It will change the format of the phone numbers to the original one.

Below, we will explain how you can format the phone numbers using Custom Number Format.

Steps:

➤ Select column D and click on the Edit option from the top part of Google Sheets.
➤ A list of options will appear, and select Find and replace.

➤ Now, a new pop-up will be opened, and in the Find option, write “=”. Keep the Replace with option blank.
➤ Then click on the Also search within formulas and select Replace all.

➤ Now, select the Find box again, remove “=”, and type “+” and then select Replace all. 

➤ Again remove “+” from the Find box and type “–”. Then, select Replace all and click Done. 

➤ Now, select Format from the top menu. A list of options will open up.
➤ Then, select Number, and another options list will pop up.
➤ Finally, select Custom number format, and another option window will show up.

➤ Now, in the Custom number format box, type “+”#”-“###”-“###”-“#### for a US phone number.

➤ Finally, select Apply, and it will return the phone numbers in the US phone number format.

Frequently Asked Questions

Can I format Phone Numbers in Google Sheets Using an Android Phone?

Yes, you can. However, you cannot change the format extensively. You can simply change the format from Number to Plain text. However, for complicated numbers, you will need the version for the desktop, or you will need to manually type it.

Can I Show The Leading Zero of a Phone Number?

Absolutely, you can. Google Sheets usually takes the phone numbers as the Number format, and so, it removes the leading zero automatically. However, you can change it simply by selecting the column containing the phone numbers, and choosing Format →Number→Plain text.

Can I Stop Google Sheets from Formatting the Phone Numbers Automatically?

Yes, you just need to select the column, set the Number format to Plain text. Then, when you paste the data into that cell, it will show up with its original look.

Wrapping Up

In this article, we learned how to change the phone number format in Google Sheets using its built-in function, Find and Replace. We also explained how to use the Custom number format to change the phone number format. Try these methods and do not hesitate to reach out if you face any issues.