Breaking up Cells

[LewisReeder]

How do I break up a cells that contain people's names? The cells contain FirstName LastName and I want each name in separate cells (for sorting purposes). Some of these cells also have names with a middle initial. I am totally lost so any advice will be helpful.

Thank you in advance.

Lew

 

[GlennUK]

The simple answer is use Text to Columns ( menu command Data/Text to Columns ), but that won't do exactly what you want because of the middle initial.

What do you want to happen to the middle initial? Do you want it as part of the first name cell? Or do you want it dropped?

What about multi word surnames? Or name suffixes, like Jr or Senior?

There are dozens of cases that might need to be considered, it depends on how comprehensive you want this processing to be.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

If it is ALWAYS FirstName LastName AND Neither FirstName nor LastName contain SPACES, use Data > Text to Columns...

If your also have middle names/initials, sufixes like Jr, III etc, names like Bobby Joe von der Voort, youf have a mess that is not easily parsed as desired.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[LewisReeder]

Glenn - The middle initial and surnames can be dropped to make things easier. I really only need the first and last name in 2 separate cells.

Thanks,
Lew

 

[SkipVought]

"The middle initial and surnames can be dropped to make things easier."

It does not really make ANYTHING easier.

How do you determine what part of the string is FIRST and what part is LAST with CERTAINTY???

If you can describe the logic with CERTAINTY, then it can be done. Otherwise, it's a mess.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[LewisReeder]

Skip -

Cant you use the LEFT and RIGHT operators somehow to take the first and last name only. I realize any surnames would be taken instead of the last name but out of the 600 names very few have surnames, so I can reconcile these manually. I also have very few multi-name people. Does this make anything easier?

Lew

 

[SkipVought]

Yes,
[tt]
=LEFT(A2,FIND(" ",A2)-1)
=RIGHT(A2,LEN(A2)-FIND(" ",A2))
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bluedragon2]

Then do a text to columns with a space as the delimiter and check anything in the third column manually.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

Really, as Blue pointed out, Text to Columns is the simplest, as I originally suggested.

There are many ways to skin a cat!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlhelp]

Try
=LEFT(A1,FIND(" ",A1)-1)
and
=IF(ISERROR(FIND(" ",A1,FIND(" ",A1)+1)),RIGHT(A1,LEN(A1)-FIND(" ",A1)), RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1)))

Not fully tested.

Vita Brevis

 

[GlennUK]

Or, to get the last name ( or last word ), you could have an array formula ( that's entered using Ctrl-Shift-Enter ) of:

=MID(A2,MAX(IF(MID(A2,ROW($1:$256),1)=" ",ROW($1:$256),0))+1,256)

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[bluedragon2]

Sorry Skip, didn't see your reference to text to columns...

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...