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




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 Burgers Building Large Lighted Marquee Letters Build a Scolling Quotation Display 
Add instructable to: