Accelerating with Excel

October 22, 2007 11:08 PM

I have a list of a few hundred people involved with the school I am at this year, and I need to break the names into First, Middle, and Last Name. Is there a way to make excel go through a list of names and at any point where there is a character break (a space like this---> ) put the rest of the cell one over to the right?
posted by daviss to Computers & Internet (13 answers total)

If I was at work I could tell you how to do it, but I can't remember off the top of my head. Try search help for "split string". If no one's answered this in 12 hours, I'll post better instructions.
posted by pombe at 11:10 PM on October 22, 2007

Best answer: Data -> Text to Columns may do what you want, with a few iterations.
posted by Jimbob at 11:13 PM on October 22, 2007 [1 favorite]

Yep.

Data>>Text to columns - delimited (next page: space).

You'll have to go through and check manually for the people with double barrelled surnames, but it'll get most of them right.
posted by b33j at 11:14 PM on October 22, 2007

I'd just open the file in notepad and find/replace all spaces with "," and save it as a csv file. Re-open in excel and watch the magic unfold
posted by kaydo at 11:16 PM on October 22, 2007

what kaydo said.

same process, really, but i'd save as txt, replace all spaces with commas, then replace all double commas with single commas, repeat as necessary (this gets rid of all the double, triple etc spaces), then open the file again in excel (it's effectively a csv file now, ie comma-separated-values). makes no difference if you call it csv or txt.
posted by UbuRoivas at 11:34 PM on October 22, 2007

hey, that data>text to columns is good!

i'd never noticed that before.
posted by UbuRoivas at 11:37 PM on October 22, 2007

I'd just open the file in notepad and find/replace all spaces with "," and save it as a csv file. Re-open in excel and watch the magic unfold

Along those lines, you could also (in Notepad) replace all spaces with tabs, and just paste it into Excel. Notepad is your best friend when messing around with Excel.
posted by apple scruff at 12:21 AM on October 23, 2007

There is no need to replace anything in the file since a space is a valid delimiter. B33j had it right.
posted by DarkoBeta at 2:13 AM on October 23, 2007

If you need to make a more advanced list, say to extract phone numbers, post codes, address etc, then read up on using Word's Advanced Find and Replace with wild cards. One of the Word MSVPs has an excellent set of primers for this.

You can massage very long, complex and messy lists quite quickly when you get the hang of it.
posted by dantodd at 5:18 AM on October 23, 2007

I'd be inclined to hit it with formulas if it's simple enough. Assuming an unvarying list of "LAST, FIRST"...

Last: =LEFT(A1,FIND(",",A1)-1)
First: =RIGHT(A1,LEN(A1)-FIND(", ",A1)-1)

The Excel MID function will get you the piece in between; I'm just too lazy to generate the formula at the moment. Reply if you get stuck.

You'll need columns for each of your name components, and once it looks like you want it to, you can convert (or "copy special") to turn the formula results into raw data.

Of course, if it gets more complicated than that—suffixes, multiple middle names, "first" names with spaces (e.g. Beth Ann)—you'll quickly exceed the practical capacity of the approach. Name data is not typically pretty. If this is something you'll be working with for more than just this once, you might want to look into Perl or some other text manipulation language.
posted by phrits at 5:37 AM on October 23, 2007

And on what should've been a better preview, b33j has it right. Wish I'd had that yesterday when I was converting m/d/yyyy to =date(...).
Day: =IF(LEN(LEFT(D16,FIND("/",D16)-1))>1,MID(D16,FIND("/",D16)+1,LEN(D16)
-FIND("/",D16,3)-5),MID(D16,FIND("/",D16)+1,LEN(D16)-FIND("/",D16,3)-2))
makes my eyes hurt. LOL.
posted by phrits at 5:49 AM on October 23, 2007

I should add that if you do text to columns, you'll want to review the results. If, for example, someone does not have a middle name (or two middle names or two last names, etc.) then that person's information will be off. It's just a hazard of using delimiters with long lists of demographic information.
posted by DarkoBeta at 6:23 AM on October 23, 2007

Ok, I just tried the text to columns thing, which I didn't know about and is very cool. (I would have solved this phrits's way.)

But be aware that once you use it, Excel will continue to parse all pasted text using the delimiter you set until you do another text-to-columns action with a different delimiter. To get things back to normal behavior you have to uncheck all the delimiter boxes. At least that's what happened with my version of Excel & what worked for me as a fix.
posted by yarrow at 7:04 AM on October 23, 2007