Breaking a Part Text Using Excel

Breaking a Part Text Using Excel

step 1 Build your list of names

In Cell A1, enter the column header Name. Frequently lists will have names in the form Last, First. Enter some names to work with. In computer lingo, a group of text characters like our names here is called a string.

step 2 Trim out extra spaces

Many times, lists from computer programs or copied from web pages have extra spaces. Excel has a function called TRIM that removes extra spaces; if there are two or more spaces in the middle of a string Excel takes out all but one, and if there are multiple spaces at the end of the string Excel removes all of them. To use the function, enter the header TRIMMED in Cell B1 then enter the formula =TRIM(A2). To show how the function works, note in the image that I added spaces in front of the first name, Jane. The TRIM function removes all of the extra spaces.

step 3 Find the delimiting character

In order to separate the last name and first name into different cells, we need to find something that divides the two parts (in computer lingo, this is called a delimiter); in our case here it is a comma.

To do this we will use the string functions in Excel. In Cell C1, enter the header Comma and in Cell C2 enter the formula: =FIND(",",B2). In English, this formula means Look in Cell B2 and find out where the comma is. When you press enter, Cell C2 will return the number 6 (if the name in Cell B2 is Smith, Jane) which is the position of the comma in the string. We perform the function on the trimmed name.

step 4 Finding the first name

Now it is time to find the first name. To do this, we will use a function called MID, which is used to pull out parts of a string from the MIDdle of the string. In Cell D1, enter the header F_Name.

Where does the first name begin? Well, since we know the comma is in position 6 (the number returned in the Comma column) and there is one space (remember, we trimmed the string) the first name must start two positions past the comma (one for the space after, then one more for the first letter in the first name). In Cell D2 enter the formula =MID(B2,C2+2,100) and press enter. You should see Jane in the cell. Here's how the formula would be translated into English: Take the string in Cell B2, start 2 characters past the comma, and pull off 100 characters. By telling Excel to take 100 characters we are saying take everything from character 8 to the end of the string; whether there's 1 character or 100 Excel will get everything.

step 5 Finding the last name

Now for the last name. This time, we will use the LEFT function, which pulls text from the left side of the string (ingenious name, eh?). In Cell E1, enter the header L_Name. So where does the last name end? We know the comma is in position six in this example, so the last name must end 1 character before that. In Cell E2 enter the formula =LEFT(B2,C2-1) and press enter. You should see Smith. Translated the formula says: Take the number in Cell C2 and subtract 1; take that many characters from the left side of the string in Cell B2.

step 6 Copy the formulae

Copy your hard-built formulae down to process the rest of the names by highlighting Cells B2 through E2. You'll notice that in the lower-right corner of the selection there's a small black box; holding down your left mouse button, select the small black box and drag down (when your mouse is hovering over the correct place, it will turn into a 'plus' sign). Hold down the left mouse button and drag down until you reach the last row of names in your list and release the left mouse button.

step 7 Finishing Up

Now you don't want formulae, you want names. We're almost finished. Highlight Cells D2 through E6. Inside the highlighted area, click your right mouse button. The border should change to a moving dotted line. Select Copy from the shortcut menu. Then, right mouse again inside the highlighted area, but this time select Paste Special. Click on the Values radio button, and select OK. Check any cell that had a formula (like D2 or E5); the formula should have been replaced by the formula's result.

Click on the A at the top of column A, and holding down you left mouse button drag to column C. Right mouse in the highlighted area and select Delete; you will be left with your list of names with the first and last names in different columns.

This is a flexible technique that you can apply to a lot of situations. Have fun!