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.
<p>great info, thanks..</p><p>Just want to share if you want to protect your sheet with a password:</p><p>http://youtu.be/EYQ900kuluY</p>
<p>How do i split &quot;1234&quot; in 1 cell to 4 cells with 1 in column 1, 2 in column 2 etc etc ?</p>
<p>This is awesome. Very easy and save me tons of time. Thanks!</p>
You're very welcome! Glad you found it worthwhile.
<p>Very Helpful. Easy to execute and logics have been explained in a comprehensive way. </p>
<div>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 <p>http://www.excel-aid.com/excel-texthorizontal-and-vertical-alignment.html</p> I think the way youexplained the functions really great, hats off.</div>
I'm very glad you found it helpful. Thanks for your kind words.
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. <br>1. bad use of memory. If you have tens of thousands of rows, wasting this much memory will also slow down processing.<br>2. what if some records go beyond 100 characters? Now you need to rewrite everything. <br>This is not worth the problems. Use the tools that were partially given here in a different format.<br>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.<br><br>Place this in D2<br>=TRIM(RIGHT(B2,FIND(&quot;,&quot;,B2)-1))
The preliminary<a href="http://www.coachs-factoryoutlets.net/" rel="nofollow" title="Coach Outlet">Coach Outlet</a><br>results extend <a href="http://www.coachs-factoryonline.net/" rel="nofollow" title="Coach Outlet">Coach Outlet</a><br>the rising <a href="http://www.coachs--outlet.net/" rel="nofollow" title="Coach Outlet">Coach Outlet</a><br>influence of <a href="http://www.chanelhandbagse.com/" rel="nofollow" title="Chanel Handbags">Chanel Handbags</a><br>Islamists across <a href="http://www.coachsoutlets.org/" rel="nofollow" title="Coach Outlet">Coach Outlet</a><br>a region where <a href="http://www.chanelbagses.com/" rel="nofollow" title="Chanel Bags">Chanel Bags</a><br>they were once <a href="http://www.coachs-factory.org/" rel="nofollow" title="Coach Store Online">Coach Store Online</a><br>outlawed and <a href="http://www.coachsoutletonline.net/" rel="nofollow" title="Coach Outlet Online">Coach Outlet Online</a><br>oppressed by <a href="http://www.coachs-outlets.net/" rel="nofollow" title="Coach Outlet">Coach Outlet</a><br>autocrats aligned<a href="http://www.coachoutleta.net/" rel="nofollow" title="Coach Store Online">Coach Store Online</a><br> with the West.<a href="http://www.coachoutletonlinea.net/" rel="nofollow" title="Coach Outlet Online">Coach Outlet Online</a><br> Islamists have<a href="http://www.louisvuittonsbag.org/" rel="nofollow" title="Louis Vuitton Bags">Louis Vuitton Bags</a><br> formed governments<a href="http://www.coachfactoryoutlete.net/" rel="nofollow" title="Coach Factory Outlet">Coach Factory Outlet</a><br> in Tunisia and <a href="http://www.louisvuittons-bags.org/" rel="nofollow" title="Louis Vuitton Bags">Louis Vuitton Bags</a><br>Morocco. They are <a href="http://www.coachs-factorys.org/" rel="nofollow" title="Coach Factory Store">Coach Factory Store</a><br>positioned for <a href="http://www.coach-outlett.org/" rel="nofollow" title="Coach Outlet">Coach Outlet</a><br>a major role in <a href="http://www.coachoutletonlinet.org/" rel="nofollow" title="Coach Factory Outlet">Coach Factory Outlet</a><br>post-Qaddafi<a href="http://www.coachs0utletsstore.net/" rel="nofollow" title="Coach Outlet">Coach Outlet</a><br> Libya as well. <a href="http://www.chanelonlines.net/" rel="nofollow" title="Coach Outlet Online">Coach Outlet Online</a><br>But it is <a href="http://www.coachoutletonlinen.com/" rel="nofollow" title="Coach Factory Outlet">Coach Factory Outlet</a><br>the victory <a href="http://www.coachoutleto.org/" rel="nofollow" title="Coach Outlet">Coach Outlet</a><br>in Egypt &mdash; <a href="http://www.chanelbagsl.net/" rel="nofollow" title="Chanel Bags">Chanel Bags</a><br>the largest<a href="http://www.chaneloutlete.org/" rel="nofollow" title="Chanel Bags">Chanel Bags</a><br> and once the <a href="http://www.chanelbagse.com/" rel="nofollow" title="Chanel Bags">Chanel Bags</a><br>most influential<a href="http://www.louisvuitton2u.net/" rel="nofollow" title="Louis Vuitton">Louis Vuitton</a><br> Arab state, <a href="http://www.coachonlines.net/" rel="nofollow" title="Coach Factory Store">Coach Factory Store</a><br>an American <a href="http://www.chanelbagse.net/" rel="nofollow" title="Chanel Handbags">Chanel Handbags</a><br>ally considered<a href="http://www.coachsoutlet2u.net/" rel="nofollow" title="Coach Outlet">Coach Outlet</a><br> a linchpin of<a href="http://www.chanelonlines.net/" rel="nofollow" title="Chanel Bags">Chanel Bags</a><br> regional stability <a href="http://www.louis-vuittonses.net/" rel="nofollow" title="Louis Vuitton">Louis Vuitton</a><br>&mdash; that has the<a href="http://www.coachsoutlette.net/" rel="nofollow" title="Coach Outlet">Coach Outlet</a><br> potential to <a href="http://www.coachsoutlett.net/" rel="nofollow" title="Coach Outlet">Coach Outlet</a><br>upend the <a href="http://www.chanelbagsl.net/" rel="nofollow" title="Chanel Bags">Chanel Bags</a><br>established order <a href="http://www.coach-factorystore.org/" rel="nofollow" title="Coach Factory">Coach Factory</a>across the Middle East.
very informational...... Thanks....!
Glad you found it useful.
sir i have an interest in developing a special software what r the requirements for that plez tel me sir
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 <br>
Of course, post your question. We're all here to help.
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.. <br><br>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..<br><br>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 <br>
I like your technique. Excel also has a built-in 'text to columns' feature ( under Data in Excel 2007 ) which does something similar.
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):<br><br>=LEFT(A5,1)&amp;&quot;.&quot;<br><br>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 &amp;) a period (inside the quotes). You can't make those kinds of on-the-fly changes to your data using text-to-columns.
If there aren't any ridiculous spaces in A1, I usually do <br>=left(A1,find(&quot;, &quot;,A1)-1) <br>for the last name (if their last name comes first in the list) and <br>=right(A1,len(A1)-find(&quot;, &quot;,A1)-1) <br>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.
Yes, that would work.<br><br>The enterprise resource planning (ERP) system I work with often pads cells with spaces, so the trim function works well.<br><br>Thank you for your comment.
Great! thanks!<br>
You are very welcome. Glad you found this useful.
&nbsp;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!!
You are very welcome!<br />
Thanks a million !!! Working on a project and this saved time.
Glad I could help!
Very good. Lots of detailed information in an easy to understand format.
thanks, learnt something new today
I always knew this was possible in Excel, I just never knew how. Very well explained, I am certain i will use this.
Good ible .. and it's compatible with Calc in <a rel="nofollow" href="http://www.openoffice.org">OpenOffice.org</a><br/>
Both of those are good to know. Thanks!
Excellent! Thanks!
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.
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


57 favorites


Bio: I am an administrator at a college, responsible for academic and student affairs. In a previous life, I was a human factors engineer and human ... More »
More by KEUrban: Building Large Lighted Marquee Letters Build a Scolling Quotation Display Volume of a Cylinder
Add instructable to: