Breaking Apart Text Using Excel




About: I've been a president at two colleges and currently provide consulting services for small businesses, non-profits, and educational organizations. In a previous life, I was a human factors engineer and human ...

This instructable will show you how to break apart (in computer lingo, parse) text using Excel. The instructable will introduce you to some of the text-handling commands in Excel. This instructable will be based on Excel 2007, but will work in any recent version of the software. The technique can be applied to a wide range of data.

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!



    • Growing Beyond Earth Maker Contest

      Growing Beyond Earth Maker Contest
    • Frozen Treats Challenge

      Frozen Treats Challenge
    • Backyard Contest

      Backyard Contest

    33 Discussions


    4 years ago on Introduction

    How do i split "1234" in 1 cell to 4 cells with 1 in column 1, 2 in column 2 etc etc ?


    5 years ago on Introduction

    Since I am working more with the functions of excel, I know excel's stuff by rote. As a beginner I helped myself with this site

    I think the way youexplained the functions really great, hats off.
    1 reply

    7 years ago on Step 4

    There are many ways to do these steps often, but given the above, it is really not the best choice in identifying the piece after the comma.
    1. bad use of memory. If you have tens of thousands of rows, wasting this much memory will also slow down processing.
    2. what if some records go beyond 100 characters? Now you need to rewrite everything.
    This is not worth the problems. Use the tools that were partially given here in a different format.
    The TRIM command is nice for removing unwanted spaces, use the FIND to locate that comma and identify how much space to the right of the comma is needed. And Voila, a perfectly formatted cell every time.

    Place this in D2


    7 years ago on Introduction

    sir i have an interest in developing a special software what r the requirements for that plez tel me sir

    1 reply

    Reply 7 years ago on Introduction

    I'm sorry. I don't understand your question because it is too general. Could you give me more information?


    7 years ago on Step 7

    Thanks, very informative and nicely done.............making understand excel language in simple English..................Gotta query............can i post

    1 reply

    8 years ago on Introduction

    good ole microsoft.. a dozen ways to do everything.. ctrl a crtl c, right click the desktop new text.. enter enter, (opens the txt file) crtl v, paste the names and comma's to the txt.. save as comma delimited, close open with excel and you will get two columns..

    of course just one more way, takes me about 1 minute.. when i need to send names back to my nortel switch via a script i can not use the comma and have to use txt to clear the carriage return at the end of each excel script paste..

    send a1 gives me the content of a1 and a return.. turning the excel into a txt, i drop the carriage return.. then i can insert the space.. another great reason to use linux.. tied the mr gates at work, but when i have the choice.. linux, dsl


    8 years ago on Step 7

    I like your technique. Excel also has a built-in 'text to columns' feature ( under Data in Excel 2007 ) which does something similar.

    1 reply

    Reply 8 years ago on Step 7

    Thanks for your comment. Text-to-columns works in a lot of cases; this technique can give you more control. You can also use it to modify the text. For instance, instead of pulling the entire first name, you could pull just the first character and then add in a period. It would be like this (I'm assuming the first name is in cell A5):


    The ampersand is a shortcut for concatenating (sticking together) two pieces of text, in this case the first character on the left, along with (by the &) a period (inside the quotes). You can't make those kinds of on-the-fly changes to your data using text-to-columns.


    8 years ago on Introduction

    If there aren't any ridiculous spaces in A1, I usually do
    =left(A1,find(", ",A1)-1)
    for the last name (if their last name comes first in the list) and
    =right(A1,len(A1)-find(", ",A1)-1)
    for the first name. The formulas assume the person entered in the names correctly in the first place though. I guess you could use these formulas for B1 since you already have it trimmed.

    1 reply

    Reply 8 years ago on Introduction

    Yes, that would work.

    The enterprise resource planning (ERP) system I work with often pads cells with spaces, so the trim function works well.

    Thank you for your comment.