Google Sheets Phone Number Format: When you type a phone number in Google Sheets, the sheet displays the number in the default style. In order to format any number in the format of a Phone number, we should apply the Google Sheets phone number format formula.

Let’s understand how to do this with an example along with important Google Sheet tips. Read on to find out more.

Table of Contents

How to Format Phone Numbers in Google Sheets?

Follow the steps as listed below to format the phone numbers in Google Sheets:

  • Step 1: Select the column where you would like to format the numbers in the format of mobile numbers.
  • Step 2: Now click on “Format” and select “Number” from the drop-down menu.
  • Step 3: Upon clicking the “Number” you will see various options. Now move to the end and select “More Formats”.
  • Step 4: Click on “Custom Number Format“.

how to format phone number in google sheets

  • Step 5: Now custom number format window will open up. Here enter the formula as ” “(“###”)”” “###”-“####
  • Step 6: Click on the “Apply” button and you will see the results.

how to format phone number in google sheets

Google Sheets Phone Number Format Formula

The phone number format varies from country to country. Thus, we have provided different phone number format formulas for few countries in the table below. Just copy-paste the following formula in the “Custom Number Format” pane to see the results in few seconds.

The following samples can help you discover the formatting you are looking for. If not, they can provide a starting point that you can tweak to fit your needs.

Style

Formula

Example

US Style Domestic “(“###”)”” “###”-“#### (123) 456-7890
US Style Domestic with dashes ###”-“###”-“#### 123-456-7890
US Style International “+1-”###”-“###”-“#### +1-123-456-7890
US Letterhead Style ###”.”###”.”#### 123.456.7890
Dialed from Germany “001-”###”-“###”-“####  001-123-456-7890
Dialed from France “191 “”###” “###” “#### 191 123 456 7890

Phone Number Format in Google Sheets – Syntax

# – It represents the total number of numbers present in a Phone number. For example, if your phone number has 10 numbers, then you will have to use # 10 times.

Likewise, you will need parenthesis, dashes, periods, spaces, numerical prefixes, and other textual information, depending on the format of the phone number.

  • Double quote marks are used in Google Sheets to denote textual information between numbers.
  • You might use a formula like #”-”# to add a dash between two numerals.
  • # denotes the next digit in the sequence of appearances.
  • To display a text use “”
  • To add a space use ” “
  • “(“ and “)” to show parenthesis
  • “-” to add dash in between numbers
  • “.” to add periods between phone numbers
  • “001” to add predetermined numbers
  • “+1-” to add a +1 international code followed by a dash.

How to Convert Phone Numbers into Normal Numbers?

We can easily convert the phone numbers into normal numbers by following the steps listed below:

  • Step 1: Select the cell where you would like to convert the phone number into a normal number.
  • Step 2: Enter the formula “=VALUE(REGEXREPLACE(CELL,”[^[:digit:]]”, “”))
  • Step 3: Now click on the “Enter” button. You will see the results.

how to format phone number in google sheets

  • Step 4: To apply the formula to desired cells, just drag the formula applied cell.
  • Step 5: Now you will see the phone numbers formatted into normal numbers in the cell.

Now copy the converted cells and go to the phone number formatted cell and paste the copied data.

Leave a Reply

Your email address will not be published. Required fields are marked *