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.

Comments

[jakesty]

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

[dilip.markule]

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

[KEUrban]

Glad you found it useful.

[bkothapalli]

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

[KEUrban]

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

[haroonshaikh786]

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

[KEUrban]

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

[johnpoole]

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

[ttibbenham]

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

[KEUrban]

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.Paj]

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]

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.

[mhacy]

Great! thanks!

[KEUrban]

You are very welcome. Glad you found this useful.

[missplumeau]

 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]

You are very welcome!

[confused73]

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

[KEUrban]

Glad I could help!

[tabgilbert]

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

[nailz7]

thanks, learnt something new today

[CAR_RAMROD]

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

[omnibot]

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

[dchall8]

Both of those are good to know. Thanks!

[mnapier61]

Excellent! Thanks!

[JakeTobak]

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]

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!