Instructables
Picture of 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.
 
Remove these adsRemove these ads by Signing Up

Step 1: Build your list of names

Picture of 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

Picture of 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.

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

(removed by author or community request)
KEUrban (author)  louella.dsouzasen10 days ago
Glad to help!
ExcelMaster7 months ago
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.
KEUrban (author)  ExcelMaster7 months ago
I'm very glad you found it helpful. Thanks for your kind words.
jakesty2 years ago
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))
very informational...... Thanks....!
KEUrban (author)  dilip.markule3 years ago
Glad you found it useful.
bkothapalli3 years ago
sir i have an interest in developing a special software what r the requirements for that plez tel me sir
KEUrban (author)  bkothapalli3 years ago
I'm sorry. I don't understand your question because it is too general. Could you give me more information?
Thanks, very informative and nicely done.............making understand excel language in simple English..................Gotta query............can i post
KEUrban (author)  haroonshaikh7863 years ago
Of course, post your question. We're all here to help.
johnpoole3 years ago
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
ttibbenham3 years ago
I like your technique. Excel also has a built-in 'text to columns' feature ( under Data in Excel 2007 ) which does something similar.
KEUrban (author)  ttibbenham3 years ago
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.
Dr.Paj3 years ago
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.
KEUrban (author)  Dr.Paj3 years ago
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.
mhacy3 years ago
Great! thanks!
KEUrban (author)  mhacy3 years ago
You are very welcome. Glad you found this useful.
missplumeau4 years ago
 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!!
KEUrban (author)  missplumeau4 years ago
You are very welcome!
confused735 years ago
Thanks a million !!! Working on a project and this saved time.
KEUrban (author)  confused735 years ago
Glad I could help!
tabgilbert5 years ago
Very good. Lots of detailed information in an easy to understand format.
nailz75 years ago
thanks, learnt something new today
CAR_RAMROD5 years ago
I always knew this was possible in Excel, I just never knew how. Very well explained, I am certain i will use this.
omnibot5 years ago
Good ible .. and it's compatible with Calc in OpenOffice.org
dchall85 years ago
Both of those are good to know. Thanks!
mnapier615 years ago
Excellent! Thanks!
JakeTobak5 years ago
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.
KEUrban (author)  JakeTobak5 years ago
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!