Alphabetize & Separating multiple words into multiple cells

**rmiranda1824** · 11-12-2010, 05:17 PM

Hello! brand new to this forum, but not forums in general. Also relatively new to Excel, but know the basics.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I have a big project that I'm working on for a possible future employer. The outcome of this project will be the deciding factor to if I get hired or not. So a speedy response is greatly appreciated.

I have ≈45,000 emails, last names, and first names in several (40-50) excel files. These files have more info then needed, so I've created one master file that has 3 columns. Last Name/First Name/EMAIL.

The files that I'm getting the information from are all a little different. Some already have these three columns that I can copy paste into the master, but some just have the contacts name in one cell (i.e. A1= John Smith)

Is there a formula that will separate words into two columns? (i.e. A1 = John, B1 = Smith)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Also is there a way to (after I have completed my master list) alphabetize the list based on Last Name, and have the other two columns (First Name/EMAIL) link to the Last Name row, so that everything in a row won't be lost? Similar to an application that has a "sort by: Name" function.

Thank you so much for your help!

**rmiranda1824** · 11-13-2010, 12:21 AM

sorry if this isn't allowed mods, but i'm gonna bump this thread b/c i need this info as soon as possible

**MickG** · 11-13-2010, 09:28 AM

Perhaps:-

Please Login or Register  to view this content.

Regards Mick

**rmiranda1824** · 11-13-2010, 01:33 PM

thank you Mick! this definitely works, but is there a way to do it across multiple cells? I tried messing around with it, like

Please Login or Register  to view this content.

but then i realized that would only show up in that single cell, so is there a way to detatch them all into diff. cells?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Also I hit a snag because some of the names are like: John J. Smith
So they show up like:

Please Login or Register  to view this content.

I was thinking, is there a way to put a minimum number of characters code in the function to get rid of things like the "J."?

I also had a similar problem with John J. Smith Ed.D.

Again, perhaps there is a minimum number of characters. perhaps =MAXA or =DMAX

thanks again!

**zbor** · 11-13-2010, 01:59 PM

Select cells -> Data -> Text to columns -> Delimited -> next -> choose Space for delimiter -> Finnish

**rmiranda1824** · 11-13-2010, 02:08 PM

which cells do i select? the empty cells that I want the data to go to or the cells with the information? also where do I put the function after selection?

(sorry, like i said, pretty new to excel lol)

**zbor** · 11-13-2010, 02:12 PM

Select data... Columns that contains your non-separated names i.e. John Smith etc

**rmiranda1824** · 11-13-2010, 02:41 PM

I don't understand what that's doing though, I have a column of names already, I select a sample of names, I do the "Text to Columns" but where do I enter the formula that I want to operate on those names?

Maybe this will show you my problem:

Please Login or Register  to view this content.

This is what I have, so if I highlight A1:A4, and do Data > Text to Column… > etc. It doesn't do anything.

I'm trying to get B1:B4 to split into the John's and Jane's, and C1:C4 to split into the Doe's and Smith's.

Thanks for helping!

**rmiranda1824** · 11-14-2010, 01:19 AM

bump no reply

**Alf** · 11-14-2010, 04:51 AM

zbor gave you the correct answer but to ilustrate this I've added a file showing one way to do this.

Asuming that your data is in column A and that there are a maximum of 3 names in a cell that you want to split.

HTH

Alf