Do you need to format phone numbers in Google Sheets?
When you format numbers in Google Sheets, you add more clarity to your data. Without formatting, every number would look the same in the spreadsheet. It will almost be impossible to tell a phone number apart from any other 10-digit data.
Google Sheets has many native formatting options from which you can choose. In addition to this, Google Sheets also makes it possible for users to create custom formats for situations where the native options are not sufficient for your needs.
In this post, you’ll see how to format a US phone number. This is one such situation where there are no native format options available.
Get your copy of the example workbook used in this post to follow along.
Format US Phone Number with Custom Number Format
The Custom number format is a feature in Google Sheets that allows you to create formatting styles for your specific need. The Custom number format feature uses special characters to create custom formatting solutions.
Follow these steps to create and use a custom format for US phone numbers.
- Select the cell ranges to which you want to apply the formatting.
- Go to the menu and select Format.
- Choose the Number option.
- Choose the Custom number format option.
+# (###) ###-####;-;-;-
- In the Custom number formats window, copy and paste this syntax to the text box.
The pound sign #
in the syntax is a character that acts as a digit placeholder.
With this custom number syntax, you create a map that directs how the numbers will appear in the spreadsheet after you apply the syntax.
- Click on the Apply command to save the format.
As soon as you hit Apply, the syntax is applied to the selected cells and the numbers are formatted in the US style.
Format US Phone Number with the TEXT Function
You can think of the TEXT function as the functional equivalent of the Custom number format. In many ways, they will return the same result.
There’s a slight difference however in that the TEXT function converts everything to string or text format, whereas Custom number format keeps values in numeric format.
Considering your dealing with telephone numbers, the TEXT function is a more appropriate tool for use. This is because no mathematical computations will be carried out on the phone numbers.
= TEXT ( number, format )
The TEXT function uses two arguments.
number
: references the number or cell containing the number or value you want to format.format
: the formatting command you want to apply to the number. The format syntax is enclosed in quotation marks.
= TEXT ( B3, "+# (###) ###-####;-;-;-" )
Using the same format syntax as in the Custom number format method, the TEXT function changes the phone numbers to US format.
Copy and paste syntax, then use the fill handle to drag down to populate the remaining cells.
Format US Phone Number with the QUERY Function
The QUERY function is another way you can use to format values in the spreadsheet. A multipurpose function, QUERY is a function that packs a lot of punch.
= QUERY ( data, query, [headers] )
data
: this argument references the range containing the data you want to query or work with.query
: This is where you use SQL-like statements to specify the type of action you want to perform on the data. Everything in thequery
argument is enclosed in quotation marks.- [
header
]: Use this argument to specify the number of rows in thedata
range that contains headers. The argument uses numbers. However, when you use a named range in the data argument, you can always set the value of headers to 1 since only the top rows will be your column header. If left blank QUERY will use its best guess to identify the column headers in the data.
Unlike the TEXT function, QUERY works on numeric values and doesn’t change their data type.
= QUERY ( B3:B7, "SELECT B FORMAT B '+# (###) ###-####;-;-;-'" )
When you copy and paste this syntax, QUERY will change the format of all the values in column B.
In the syntax, SELECT B
identifies which column from the data with which you want to work on and FORMAT B '+# (###) ###-####;-;-;-'
applies the formatting instructions.
The QUERY function is an array function. When you enter the formula in the top cell, it auto-fills the remaining cells.
Format US Phone Number with the MID Function
The MID function extracts any number of characters from any given location within a specified string.
=MID(string, starting_at, extract_lenght)
MID has three arguments.
string
: The text or cell containing the string from which you want to extract.starting_at
: The index of the position from the left of thestring
from where you want to start the extraction. For example, to return “traction” from “extraction”, thestarting_at
argument will be 3, since the index of “t” in “extraction” is 3.extract_lenght
: beginning with thestarting_at
position, the number of characters to be returned.
= "+" & MID ( B3, 1, 1 ) & " (" & MID ( B3,2 ,3 ) & ") " & MID ( B3, 5, 3 ) & "-" & MID ( B3, 8, 4 )
The above syntax will return the phone numbers in US format.
Here’s a breakdown of every part of the formula.
“+”: this provides the plus sign that precedes the country code. It’s enclosed in quotation marks, so the formula reads it as a string and doesn’t throw an error.
&MID(B3, 1, 1)
: the first MID function in the syntax returns the first number from the string and adds it to the “+” sign using the ampersand notation. From cell B3, the MID function returns 1 so that the extracted characters up to this point are +1.
&" ("&MID(B3, 2, 3)&") "
: after extracting the first number from the string, the second MID function returns the next three numbers and puts them in parenthesis, and leaves a space after. That is, from B3, this MID function returns (780). At this point, the extracted values are +1 (780).
&MID(B3, 5, 3)
: this mid function starts extraction from the 5th character in the string and extracts 3 numbers. Also, from cell B3, this function will return 555 so that the extracted characters to this point would be +1 (780) 555.
"-"&MID(B3, 8, 4)
: a hyphen precedes the last 4 characters that are extracted using this formula. The formula returns the character -1234 from cell B3. Adding this to the characters extracted and formatted earlier, the result becomes +1 (780) 555-1234.
After copying the formula to the cell, you can drag it down using the fill handle to copy the formula to other cells.
Format US Phone Number with an Apps Script
The previous methods discussed so far require that you understand how to create a custom format and/or understand the special characters used for creating one.
With this apps script solution, you can format a US phone number in two easy clicks.
To use the apps script solution, first, you have to copy and paste the script below into your apps script editor.
To open the apps script editor window, go to the Extensions menu and select Apps Script.
function usNumber(){
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getActiveRange();
var values = range.getValues();
var selectedColumns = range.getWidth();
var selectedRows = range.getHeight();
for(i = 0; i < selectedRows; i++) {
for(j = 0; j < selectedColumns; j++) {
if(range.getCell(i + 1, j + 1).isBlank()) {
throw " "
} else {
value = values[i][j].toString();
var sOne = value.slice(0, 1);
var sTwo = value.slice(1, 4);
var sThree = value.slice(4, 7);
var sFour = value.slice(7, 12);
var number = "+" + sOne + " " + "(" + sTwo + ")" + " " + sThree + "-" + sFour;
range.getCell(i + 1, j + 1).setValue(number);
}
}
}
}
function onOpen() {
SpreadsheetApp.getUi()
.createMenu("Format Phone Number")
.addItem('US', 'usNumber')
.addToUi();
}
After pasting the script, click on the Save and Run icon. Grand the necessary permissions, then go back to your spreadsheet window and refresh it.
The script creates a new custom menu, Format Phone Number. You can use the US submenu to change the phone number format.
Before you use this command, first select the range containing the numbers you want to format.
Conclusion
Formatting your data makes it clear and understandable to the user.
When you want to format US phone numbers, you can use any of the custom formats discussed, either by using the TEXT, QUERY or Custom number format option. Using either one of these methods will get you comfortable creating custom formats for any situation.
However, the apps script method is a quick and easy solution for times when you just need a quick fix.
Do you use any other method to format US phone numbers? Let me Sknow in the comments section!
0 Comments