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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> data_diva
> ------------------------------------------------------------------------
> data_diva's Profile:
> http://www.excelforum.com/member.php...o&userid=28885
> View this thread: http://www.excelforum.com/showthread...hreadid=486339
>

[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

--
Kind regards,

Niek Otten

"data_diva" <[email protected]> wrote
in message news:[email protected]...
>
> 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.
>
>
> --
> data_diva
> ------------------------------------------------------------------------
> data_diva's Profile:
> http://www.excelforum.com/member.php...o&userid=28885
> View this thread: http://www.excelforum.com/showthread...hreadid=486339
>

[Ralphael1]

data_diva wrote:
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> --
> Kind regards,
>
> Niek Otten
>
> "data_diva" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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.
>>
>>
>> --
>> data_diva
>> ------------------------------------------------------------------------
>> data_diva's Profile:
>> http://www.excelforum.com/member.php...o&userid=28885
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=486339
>>
>
>

[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

--
Kind regards,

Niek Otten

"Biff" <[email protected]> wrote in message
news:uPCBM%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>> --
>> Kind regards,
>>
>> Niek Otten
>>
>> "data_diva" <[email protected]>
>> wrote in message
>> news:[email protected]...
>>>
>>> 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.
>>>
>>>
>>> --
>>> data_diva
>>> ------------------------------------------------------------------------
>>> data_diva's Profile:
>>> http://www.excelforum.com/member.php...o&userid=28885
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=486339
>>>
>>
>>
>
>