loading
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.

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.
 
NubiP3 months ago

great info, thanks..

Just want to share if you want to protect your sheet with a password:

http://youtu.be/EYQ900kuluY

choy.saan7 months ago

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

dannice24010 months ago

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

KEUrban (author)  dannice24010 months ago
You're very welcome! Glad you found it worthwhile.

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

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)  ExcelMaster1 year ago
I'm very glad you found it helpful. Thanks for your kind words.
jakesty3 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)  haroonshaikh7864 years ago
Of course, post your question. We're all here to help.
johnpoole4 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
ttibbenham4 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)  ttibbenham4 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.Paj4 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.Paj4 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.
mhacy4 years ago
Great! thanks!
KEUrban (author)  mhacy4 years ago
You are very welcome. Glad you found this useful.
missplumeau5 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)  missplumeau5 years ago
You are very welcome!
confused736 years ago
Thanks a million !!! Working on a project and this saved time.
KEUrban (author)  confused736 years ago
Glad I could help!
tabgilbert6 years ago
Very good. Lots of detailed information in an easy to understand format.
nailz76 years ago
thanks, learnt something new today
CAR_RAMROD6 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.
omnibot6 years ago
Good ible .. and it's compatible with Calc in OpenOffice.org
dchall86 years ago
Both of those are good to know. Thanks!
mnapier616 years ago
Excellent! Thanks!
JakeTobak6 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)  JakeTobak6 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!