Split Contact Name

[Mohanmoni]

Hi,

I am looking for formula that can split contact name properly. I have 1,000+ Contact names like below..

Jennifer Monroe Monroe, Jennifer
Ali Talai Talai, Ali
Kim Millman Millman, Kim
Stephen E Wood Wood, Stephen E

In above example person name is repeated 2 times with ",". Now I want proper name by splitting this. Please suggest.

Thank You,
Mohan S

 

[jtakw]

Hi,

It might be a good idea to let us know exactly what you mean by proper name, is it Jennifer Monroe or Monroe, Jennifer that you want?

 

[Rick Rothstein]

To get First Name Last Name use this formula...

=TRIM(LEFT(A1,LEN(A1)/2))

To get Last Name, First Name use this formula...

=TRIM(RIGHT(A1,LEN(A1)/2))

 

[Mohanmoni]

Thanks Rick Rothstein it works.

 

[kvsrinivasamurthy]

ARRAY formula in B1 then drag down.

=RIGHT($A1,LEN($A1)-LARGE(IF(MID($A1,ROW(INDIRECT("$1:$"&LEN(A1))),1)=" ",ROW(INDIRECT("$1:$"&LEN(A1))),""),1))&", "&LEFT($A1,LARGE(IF(MID($A1,ROW(INDIRECT("$1:$"&LEN(A1))),1)=" ",ROW(INDIRECT("$1:$"&LEN(A1))),""),1)-1)

ARRAY formula is used


To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.
formula will be covered with{} brackets by excel.

 

[Rick Rothstein]

To get First Name Last Name use this formula...

=TRIM(LEFT(A1,LEN(A1)/2))

To get Last Name, First Name use this formula...

=TRIM(RIGHT(A1,LEN(A1)/2))

In thinking about it a little more, we can simplify these formulas slightly...

To get First Name Last Name use this formula...

=LEFT(A1,LEN(A1)/2-1)

To get Last Name, First Name use this formula...

=RIGHT(A1,LEN(A1)/2)

 

[kvsrinivasamurthy]

A non ARRAY approach

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",200)),200))&", "&TRIM(SUBSTITUTE(A1,TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",200)),200)),""))