Basic Parsing of Phone Numbers in Excel

To efficiently search for phone records, it is necessary to break down a phone number into Telco components and back again. The primary components of a phone number (NPA-NXX-XXXX xEXT) are:

How to merge first name and last name in excel
Step 1 - Add Three New Columns
Parsing Columns in Microsoft Excel - Right click on the selected column and choose Insert from the menu.

Add Three New Columns

Create three new columns to accommodate the values for the separation of the phone number into its components.

  • Area Code = Number Planning Area (NPA)
  • Prefix = Central Office Code (NXX)
  • Postfix = Last four digits (XXXX)
Step 2 - Write Parsing Formula
Parsing Columns in Microsoft Excel - Select the first cell in the new column in the same row that the first name is listed.

Writing Area Code Formula

Write a small formula that........... In the Area Code (or NPA) cell, enter the function:

=LEFT(A2,3)
Parsing Columns in Microsoft Excel - Write a merge formula into the selected cell. All formulas start with an equal sign (=).

Enter Prefix Formula

In the Prefix (or NXX) cell, enter the function:

=RIGHT(left(A2,6),3)
Parsing Columns in Microsoft Excel - The formula will merge the first and last name from columns B and C into the cell.

Enter Postfix Formula

In the Postfix (or XXXX) cell, parse out the final four digits of the phone number.

=RIGHT(A2,4)
Parsing Columns in Microsoft Excel - The formula will merge the first and last name from columns B and C into the cell.

Formula Result

This shows the end result. The functions should be copied and applied to each line with a phone record that needs to be parsed.

Step 3 - Copy Formula
Parsing Columns in Microsoft Excel - Select the new cell where the new value is listed.

Copy Formula

Now you need to copy the formula to the other cells in the column. Select the new cell where the new value is listed.

Parsing Columns in Microsoft Excel - Click and drag the handle down to select the remaining column of listings.

Click and Drag

Click and drag the handle (indicated by a cross at the bottom right corner of the selected cell) down to select the remaining column of listings.

Parsing Columns in Microsoft Excel - The formula is copied to the remaining cells and the values are automatically calculated.

Result

The formula is copied to the remaining cells and the values are automatically calculated.

Step 4 - Paste Formula
Parsing Columns in Microsoft Excel - With the cells still selected, right click on your mouse and choose Copy from the menu.

Paste Formula (optional)

After the formulas have been applied to all cells in the three new columns, you will need to replace the formulas with the resulting values from the formula. Currently each cell now has a formula entered into it. You need to replace the formula with the resulting values. First, select the cells in the three new columns. Next, copy the cells by right clicking on the selected cells and choosing "copy" from the menu.

Parsing Columns in Microsoft Excel - Right click again and choose Paste Special from the menu.

Paste Special

Right click again and choose Paste Special from the menu.

Parsing Columns in Microsoft Excel - Right click again and choose Paste Special from the menu.

Paste Special

From the Paste Special dialog box, make sure Values is checked from the paste menu. This will make sure that the values are pasted into the cells.

Finished
Parsing Columns in Microsoft Excel - From the Paste Special dialog box, make sure Values is checked from the paste menu.

Finished

Congratulations! You have parsed columns. Use this simple process to parse any number of columns that you need.