Splitting first and last names in Microsoft Excel

	A	B	C
1	Mary Post Rogers	=LEFT(A1,SEARCH(" ",A1))	=RIGHT(A1,LEN(A1) - SEARCH(" ",A1))
2	Peter Van Der Groff	Peter	Van Der Groff

If you are working with excel and are given a list of names - splitting them into first and last can present problems when people have multi-word last names or first names (example: James Van Der Beek).

Its not possible to catch all of these - so you should hand check your data, but you can split single word first names and multi-word last names using the formulas above. The formulas in the first row will identify a first name and multi-word last name in addition to single word first and last name individuals.

Splitting City-State in Excel

If you have data in the following format:

	A	B
1	Cedar Rapids, IA
2	Des Moines, IA
3	San Diego, CA
4	Chicago, IL

You can split the city and state by taking advantage of the comma that has been placed in between the two. You can use Text to Columns.

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.