last name, first name strings

[data_diva]

Hi there,

I am a newbie and am having some trouble with seperating data in
Excel.

Here is my issue:

I have a table of employee names are in one column alone, but all in a
different format:

first name -space- last name
last name, first name
last name -space- first name

What I am needing to do is get all the names to be in the same order
and seperated into two columns as:

Last Name column
First Name column

How do I take those combined name formats that are in one column and
seperate them into seperate columns in the correct order?

Thanks,

Christina

 

[Biff]

Hi!

Consider this:

Marion Ross

Which is the first name and which is the last?

Biff

 

[data_diva]

I can check the names in a system and pretty much am familiar with the
employee names, its just that they are in all formats in one column and
I need them all to go the same way.

 

[Niek Otten]

Introduce one extra column in which you manually put the name type: 1, 2 or
3
So column A is the name, column B is the type.

Column C:
=CHOOSE(B1,RIGHT(A1,LEN(A1)-FIND("
",A1)),LEFT(A1,FIND(",",A1)-1),LEFT(A1,FIND(" ",A1)))
Column D:
=CHOOSE(B1,LEFT(A1,LEN(A1)-LEN(E1)),RIGHT(A1,LEN(A1)-LEN(E1)-1),RIGHT(A1,LEN(A1)-LEN(E1)))

Copy both down as far as needed

 

[Ralphael1]

I can check the names in a system and pretty much am familiar with the
employee names, its just that they are in all formats in one column and
I need them all to go the same way.

Do like I do and use the K.I.S.S. method. (Keep It Simple Stupid)
Bite the bullet: Insert two columns then manually enter each name in
the same format.
You then can delete the mixed up column to eliminate the confusion.
I just wonder why thed names were not all entered in the same format.

Ralphael, the OLD one

 

[Biff]

Hi!

That's a pretty good effort but still fails under some fairly common
situations:

Mary Beth Smith
T. Boone Pickens
Juan Carlos De Santos

=CHOOSE(B1,LEFT(A1,LEN(A1)-LEN(E1)),RIGHT(A1,LEN(A1)-LEN(E1)-1),RIGHT(A1,LEN(A1)-LEN(E1)))

Think the references to E1 should be to C1.

Not meaning to be a "jerk", but sometimes things that appear to be fairly
easy and straightforward at face value aren't so easy after all. I'm sure
you know that!

Parsing names is still "easier" than parsing mailing addresses!

Biff

 

[Niek Otten]

<but sometimes things that appear to be fairly easy and straightforward at
face value aren't so easy after all. I'm sure you know that!>

I sure do. I just followed your instructions and tested the formulas for
them
I'm sure with the help so far you can figure out whatever variations you may
have!

If not, don't hesitate to post again in this same thread, with full
specifications please