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“.
- 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.
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.
- How To Insert Indents in Google Sheets? – Know How to Tab Down in Google Sheets
- How to Convert Excel to Google Sheets: Saving Excel Sheet in Google Drive
- How to Rotate Text in Google Sheets: Stack, Flip, Rotate Upside Down in G-Sheet
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.
- 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.