Introduction: Breaking Apart Text Using Excel

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!

Comments

author
choy.saan made it! (author)2015-01-22

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

author
dannice240 made it! (author)2014-11-04

This is awesome. Very easy and save me tons of time. Thanks!

author
KEUrban made it! (author)KEUrban2014-11-04

You're very welcome! Glad you found it worthwhile.

author
louella.dsouzasen made it! (author)2014-10-12

Very Helpful. Easy to execute and logics have been explained in a comprehensive way.

author
KEUrban made it! (author)2014-10-10

Glad to help!

author
ExcelMaster made it! (author)2014-03-18
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

http://www.excel-aid.com/excel-texthorizontal-and-vertical-alignment.html

I think the way youexplained the functions really great, hats off.
author
KEUrban made it! (author)KEUrban2014-03-18

I'm very glad you found it helpful. Thanks for your kind words.

author
jakesty made it! (author)2011-12-11

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
=TRIM(RIGHT(B2,FIND(",",B2)-1))

author
dilip.markule made it! (author)2011-10-08

very informational...... Thanks....!

author
KEUrban made it! (author)KEUrban2011-10-09

Glad you found it useful.

author
bkothapalli made it! (author)2011-09-20

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

author
KEUrban made it! (author)KEUrban2011-09-21

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

author
haroonshaikh786 made it! (author)2011-07-15

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

author
KEUrban made it! (author)KEUrban2011-07-16

Of course, post your question. We're all here to help.

author
johnpoole made it! (author)2011-06-13

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

author
ttibbenham made it! (author)2011-05-14

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

author
KEUrban made it! (author)KEUrban2011-05-14

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):

=LEFT(A5,1)&"."

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.

author
Dr.Paj made it! (author)2010-12-29

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.

author
KEUrban made it! (author)KEUrban2010-12-29

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.

author
mhacy made it! (author)2010-10-26

Great! thanks!

author
KEUrban made it! (author)KEUrban2010-10-26

You are very welcome. Glad you found this useful.

author
missplumeau made it! (author)2010-01-15

 I wish I had found your great Instructable years ago! I am going to use it a lot from now on. Thank you for this clear and very well written Ible!!

author
KEUrban made it! (author)KEUrban2010-01-15

You are very welcome!

author
confused73 made it! (author)2009-05-28

Thanks a million !!! Working on a project and this saved time.

author
KEUrban made it! (author)KEUrban2009-05-31

Glad I could help!

author
tabgilbert made it! (author)2009-02-02

Very good. Lots of detailed information in an easy to understand format.

author
nailz7 made it! (author)2009-01-29

thanks, learnt something new today

author
CAR_RAMROD made it! (author)2009-01-28

I always knew this was possible in Excel, I just never knew how. Very well explained, I am certain i will use this.

author
omnibot made it! (author)2009-01-28

Good ible .. and it's compatible with Calc in OpenOffice.org

author
dchall8 made it! (author)2009-01-27

Both of those are good to know. Thanks!

author
mnapier61 made it! (author)2009-01-27

Excellent! Thanks!

author
JakeTobak made it! (author)2009-01-27

There's a tool called "Text to Columns" that does this pretty easily. It's under Data tab and in the Data Tools section in 2007, idk where it is in earlier versions. You can tell it to deliminate by a couple of common choices like tabs, commas and spaces or define your own. You might still need to use the Trim function though if you have extra spaces.

author
KEUrban made it! (author)KEUrban2009-01-27

Thanks for the comment, Jake. I find that Text-to-Columns acts flaky from time to time; if you can write a formula you can eke out a little more control. But for sure, try text-to-columns first, because easy and quick is ALWAYS better :-) You can never know too many formulas in Excel!

About This Instructable

177,866views

68favorites

License:

Bio: I am a president at a college. In a previous life, I was a human factors engineer and human performance researcher. I have graduate degrees ... More »
More by KEUrban:Techniques for Foolproof Sous Vide BurgersBuilding Large Lighted Marquee LettersBuild a Scolling Quotation Display
Add instructable to: