last name, first name strings

  • Thread starter Thread starter data_diva
  • Start date Start date
D

data_diva

:confused: 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 :confused:
 
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.
 
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
 
data_diva said:
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.
Click to expand...
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
 
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)))
Click to expand...

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
 
<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
 
Back
Top