How to change the phone number format in Excel

By SpreadCheaters
Page last updated: 27/04/2023 | Next review date: 27/04/2025
close

Author bio


SpreadCheaters
View More north_east

Excel is a spreadsheet software developed by Microsoft that is used to structure ways of viewing, analysis, and visualization. It provides an array of features and tools for performing computations, generating graphs and charts, and automating. Excel allows us to Change the format of phone numbers in Excel spreadsheets, which is a practical ability that can improve the way we present data. 

In this tutorial, we’ll take you step-by-step over the process of altering the phone number format in Excel so that we can learn this crucial skill and advance our data presentation abilities. In the following dataset shown above, we have the Contact Numbers of various Students.

Method 1: Built-in Excel Format

Step 1 – Select the cell or range of cells.

  • Select the cell or range of cells containing the phone numbers that we want to format.
  • In our case, we’ve selected B2:B11.

Step 2 – Format the selected cells.

  • Right-Click on the selected cells. 
  • Choose “Format Cells” from the popup menu that appears.
  • This will open the ‘‘Format Cells’’ dialog box.

Step 3 – Select a special option.

  • Select the Number Tab.
  • From the list of Categories, Select the “Special” option.
  • We will see a list of formatting ‘Type’ options on the right side of the dialog box.

Step 4 – Select the formatting type.

  • Select the ‘Phone Number’ option from the list.
  • Click on the ‘OK’ Button.

METHOD 2: Custom Format

Step 1 – Select the required cell.

  • Select the cells containing the phone numbers that we want to format.

Step 2 – Format the selected cells.

  • Right-Click on the selected cells.
  • Choose “Format Cells” from the popup menu that appears.
  • This will open the ‘‘Format Cells’’ dialog box.

Step 3 – Select the custom option.

  • Select the Number Tab 
  • From the list of Categories, Select the “Custom” option.
  • We will see a list of formatting ‘Type’ options on the right side of the dialog box.

Step 4 –  Create the format type you want.

  • Modify our selected number format in the Type box, so that we create the exact format that we want. In this case, we want this format  (000) 000-0000
  • Click OK.

Method 3: Text Function

Syntax of the Text Function:

The syntax for theText Function in the Excel spreadsheet is as follows:

=TEXT(value, “format_text“)

Where:

  • value: the value that we want to format
  • format_text: the format that we want to apply to the value

Step 1 – Type the formula.

  • Enter the formula “=TEXT(B2,”(000) 000-0000″)” in an appropriate cell.

Step 2 – Click on Enter Button. 

  • Press Enter to apply the formula to the selected cell.

Step 3 – Implement the formula for the whole range.

  • Now implement the formula to the whole dataset by dragging down the fill handle, as shown above.

Conclusion:

Changing our phone numbers’ formatting only impacts the way they appear on the Excel sheet. It does not affect the character or value of the underlying number itself. Changing phone number format in Excel is an important skill that can help us to present data more effectively. With this skill, we can create a consistent visual style across our worksheets.