Display First name and Last Name from Text

[Anupama]

Hi,

Can you please help me with the below query. I have a (text) name field with a comined name and I want to separate them into first and last names.

For instance,
Text field : Adam Bh Chris
I want the result to be : Chris, Adam (i.e, last name comma first name)

Also,
if Text Field : Adam Chris
Result should be : Chris, Adam.

Basically, I want to eliminate any middle characters/name and display only first and last name

I hope the query is clear. Please help me finding a solution.

TIA,
Anu

 

[udprocks]

Hi,

Can you please help me with the below query. I have a (text) name field with a comined name and I want to separate them into first and last names.

For instance,
Text field : Adam Bh Chris

I want the result to be : Chris, Adam (i.e, last name comma first name)

I hope the query is clear. Please help me finding a solution.

TIA,
Anu

Hallo Anu,

for this u can apply "text to column first" for seprate names and after it u can "concatenate" for combined "Last name, first name"

test it and if not solved can u please provide sample file for it.

 

[Sinu]

in cell A1 put the name and in cell B1 put the formula below

=RIGHT(A1,FIND(" ",A1)-1) & ", " & LEFT(A1,FIND(" ",A1)-1)

 

[Mike H..]

Hi,

Can you please help me with the below query. I have a (text) name field with a comined name and I want to separate them into first and last names.

For instance,
Text field : Adam Bh Chris
I want the result to be : Chris, Adam (i.e, last name comma first name)

Also,
if Text Field : Adam Chris
Result should be : Chris, Adam.

Basically, I want to eliminate any middle characters/name and display only first and last name

I hope the query is clear. Please help me finding a solution.

TIA,
Anu

Hi,

With your string in A2 try this and drag down as required.

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",256)),256))&", "&LEFT(A2,FIND(" ",A2)-1)

 

[Anupama]

Hi,

With your string in A2 try this and drag down as required.

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",256)),256))&", "&LEFT(A2,FIND(" ",A2)-1)

This worked!
Thanks a ton! !