Splitting a telephone number in Microsoft Excel

If you have a list of telephone numbers formatted rather messily, you can split the telephone numbers into area code + local number using Find & Replace, Left(), Mid(), and Right().

	A	B	C	D
1	(555)-767-9000
2	888-487-8000
3	9994879000

The first step is to remove all of the extra characters so all your phone numbers do not include any additional characters. Use Find and Replace to remove the parentheses and dashes.

1. Select Column A
2. Press Ctrl+F to open the find and replace dialogue.
3. Under Find what: your going to enter each character you see in your phone numbers, one at a time. In our example, it would be: ( ) - . Under Replace with: you enter nothing.

You should be left with data that looks like this:

	A	B	C	D
1	5557679000
2	8884878000
3	9994879000

Now you can use the left(), right(), and mid() functions to extract each portion of the telephone number into its own column.

	A	B	C	D
1	5557679000	=LEFT(A1,3)	=MID(A1,4,3)	=RIGHT(A1,4)
2	8884878000	888	487	8000
3	9994879000	999	487	9000

The above table shows you the functions used in row 1 and the results produced in rows 2 and 3.
The left() function grabs the area code by taking the three leftward digits of the phone number. The last four digits are grabbed by right(). Mid() is used to grab the middle digits. You have to specify three pieces of information to use Mid(): The phone number, a starting point, and the number of characters to grab. In this example, we want to grab a portion of the phone number in A1, starting at the 4th character ('7'), and continuing for 3 characters.