changing the order of a text in a column - lastname, first name

[lezawang]

Hello
I just like to pick your brain. I have a column which has the firstname and lastname (one space between them). I want a new column which has the "lastname, firstname". One way I am thinking of is to use Text-to-Columns to separate the firstname from the lastname, then I will use =Concatenate(lastname,",",",firstname). Is that the only way? Do you have other ideas to do the same? Thank you so much.

[TABLE="width: 500"]
<tbody>[TR]
[TD]right now[/TD]
[TD]what i want[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Smith, John[/TD]
[/TR]
[TR]
[TD]Mary Clark[/TD]
[TD]Clark, Mary[/TD]
[/TR]
[TR]
[TD]Adam Jones[/TD]
[TD]Jones, Adam[/TD]
[/TR]
</tbody>[/TABLE]

 

[admiral100]

Hi,

Try this :

=TRIM(RIGHT(A2,LEN(A2)-(FIND(" ",A2))))&", "&LEFT(A2,FIND(" ",A2))

 

[barry houdini]

This formula should do it.....

=MID(A2&", "&A2,FIND(" ",A2)+1,LEN(A2)+1)