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.

Using Inkscape to improve Stata graphics

Stata produces graphics that can be hard to work with and are often ugly. Often times, I have trouble with labels running into one another or blocky looking elements. Moreover, I sometimes need to change a title, add a caption, or change a color and reproducing the graph in Stata to do this is both time consuming and annoying. Luckily, I have found an easy way to edit graphs in Stata, improve there look, and tweak them if need be. You use a program called Inkscape.

Concatenation in Microsoft Excel

There are two ways to concatenate (merge the contents of two cells) in Microsoft Excel.

	A	B	C	D	E
1		Item 1	Item 2	Function	Result
2	Method 1	Hello	World!	=CONCATENATE(B2,C2)	Hello World
3	Method 2	This is a way to	Concatenate	=B3 & C3	This is a way to Concatenate

The first method uses the =CONCATENATE([item 1], [item 2], [item ...]) function. Each cell specified within this function will be joined.

Alternatively, you can simply use the & symbol to join the contents of two cells.