Telephone numbers are unique numbers created to aid communication between two or more individuals over a large distance. These numbers consist of two components which are the area code and the personal code.
The area code differs for each country where an individual is located while the personal code is a distinct compilation of numbers assigned to each user by the telecommunications network. The length of phone numbers varies from one digit to eleven digits.
The world is currently in the modern age where information and technology evolve daily. Every individual has access to an electronic device which is mostly used for communications. Therefore, almost everyone currently has a phone number that boosts the transmission of information.
As a Google sheet user, one might want to input numerous sets of phone numbers for contact references or as an important component of the data inputted in the worksheet.
These numbers might be submitted in a jam-packed way which makes them less appealing and reduces clarity and reading flow. This is where we want to format the phone numbers to make them more concise.
So, In this blog we will show How to Format Phone Numbers in Google Sheets using different methods. You can follow any of this to do that job. So, let’s go.
How To Format Phone Numbers In Google Sheets.
In this tutorial, we are going to format phone numbers using different methods. There are three known methods used to format phone numbers.
Using the sample worksheet below, we would format the phone numbers of each employee who signed up for a job interview using each method.
Method 1: Format Phone Numbers with a Single Quote.
This method is most suitable for formatting phone numbers typed manually into the worksheet. For example, you want to type in the phone number, +1-555-876-1003 but due to the addition and subtraction signs in the phone number, the Google sheets programming would think you want to add and subtract the array of digits.
If you type the phone number in and click enter, it produces a result like this.
To prevent this we add a single quotation mark (‘) in front of the phone number like this.
‘+1-555-876-1003
The single quote informs the Google sheets programmed that you do not want to add and subtract the figures together, but input the phone number as it is.
Click Enter and the single quote disappears from the phone number but the digits remain as it is.
This method is not suitable for large sets of phone numbers because it is a slow method and wastes time. But it does perform its format function efficiently.
Method 2: Format Phone Numbers with Formula.
This method involves the use of a formula. The formula below formats the phone number.
=”<phone number =” “goes=” “here =” “>.<phone>
This formula is quite complex so most users might not understand how to use this formula.
Method 3: Format Phone Numbers with the Custom Number Format.
This technique is most suitable for formatting large sets of phone numbers. It is very easy to understand and operate by following the listed steps below.
Step 1: Select the whole column or the cells containing the phone numbers.
Step 2: Click on Format on the toolbar.
Step 3: Click on Number.
Step 4: Select Custom number Formats.
Step 5: Enter the type of format or style you want to apply. For this dataset, we would apply the U.S International format. So we type, +1(###)-###-####
Step 6: Click Apply.
Step 7: All the phone numbers have the format applied. They are better when viewed and more appealing.
Other Phone Number Formats
Various formats can be used in place of the U.S International format depending on the area code and service code used in that country. Examples are:
● U.S Domestic: (###) – ### – ####
● U.S Domestic dashes: ### -###-####
● U.S International: +1-(###)-###- ####
● U.S Letterhead Style: ###.###.####
● Australia Domestic: +61-#-####-####
● Iceland Domestic: +354-###-####
● France Domestic: +33-###-######
● Germany Domestic: +49-###-#######
● Hungary Domestic: +06-###-####
● Switzerland Domestic: +41-7-###-##-##
● Russia Domestic: +7-958-###-##-##
● Kenya Domestic:+254-###
● Nigeria Domestic: +234-##-###-###
● India Domestic: +22-####-####, etcetera.
Removing Existing Formatting.
When the data set of phone numbers consists of multiple numbers that are formatted or wrongly formatted and are mixed with some that weren’t formatted in the first place.
This is when we want to remove the wrong formatting and reformat them in the right style and format. To remove existing formatting, the formula below is required.
=VALUE(REGEXREPLACE(cell reference, ” [^(:digit:)]”, ” “))
This formula deletes non-digit values from the worksheet. It is a combination formula consisting of the VALUE and REGEXREPLACE functions.
The VALUE function is used to convert any part of a text, be it a date, time or number, into a number. The REGEXREPLACE function is capable of replacing a part of a text with another text, based on similar conditions.
The cell reference shows the formula, the cell enclosing the formatted phone number.
With the steps stated below, we can remove the wrong formatting from the dataset.
Step 1: Select the column or range of cells containing the formatted numbers and the normal digits.
Step 2: Right-click on the column. A menu of options is displayed. Select Insert One Column right.
This creates an extra column on the right side of the original column.
Step 3: A new column is created on the right side.
Create a title for the column. I named mine as Unformatted Phone Numbers.
Step 4: Input the formula into the new column. This is where the results would be pasted. Substitute cell reference as A2.
=VALUE(REGEXREPLACE(A2, ” [^(:digit:)]”, ” “))
Step 5: Highlight the cells below and right-click to paste the results of the other phone numbers.
Step 6: Right-click on the new digits and Click on Paste Special.
Step 7: Select Values Only or press the Ctrl + Shift + V keys on your keyboard.
This removes the formula used in calculating earlier, completely leaving the digits or values only. This makes the dataset liable for any new formatting to be done.
Final Thoughts:
Now, you know How to Format Phone Numbers in Google Sheets using 3 different methods. With the detailed procedures and steps stated above, formatting phone numbers are made straightforward to use and operate.
I hope this guide fully performs its purpose of disseminating this topic properly for better understanding and it becomes useful to you. Thank you for reading.
