Extract First Name Last name

[patneel]

Hi,

I have data stored as Last Name, First name but I want to get it now First Name Last Name (no comma)

Example-
Data-
Ho, Joseph zu-sien

Required Result-
Joseph zu-sien Ho


Also the lenghth of each name would be different so can not use fixed number to get required result.


Appreciate any help..

 

[Aladin Akyurek]

Hi,

I have data stored as Last Name, First name but I want to get it now First Name Last Name (no comma)

Example-
Data-
Ho, Joseph zu-sien

Required Result-
Joseph zu-sien Ho


Also the lenghth of each name would be different so can not use fixed number to get required result.


Appreciate any help..

If comma after the last name is a structural feature with all entries...

=REPLACE(A2,1,FIND(",",A2),"")&" "&LEFT(A2,FIND(",",A2)-1)

where A2 houses a name entry.

 

[diddi]

vba or cell data?

 

[Rick Rothstein]

If comma after the last name is a structural feature with all entries...

=REPLACE(A2,1,FIND(",",A2),"")&" "&LEFT(A2,FIND(",",A2)-1)

where A2 houses a name entry.

I think you need to change your formula to this so that it will not return the name with a leading space in front of it...

=REPLACE(A2,1,FIND(",",A2)+1,"")&" "&LEFT(A2,FIND(",",A2)-1)

 

[Rick Rothstein]

If there is always a space after the comma...

=MID(A1&" "&A1,FIND(",",A1)+2,LEN(A1)-1)

otherwise...

=TRIM(MID(A1&" "&A1,FIND(",",A1)+1,LEN(A1)))

 

[Vidar]

(deleted, almost same as above)

 

[Aladin Akyurek]

I think you need to change your formula to this so that it will not return the name with a leading space in front of it...

=REPLACE(A2,1,FIND(",",A2)+1,"")&" "&LEFT(A2,FIND(",",A2)-1)

Better:

=TRIM(REPLACE(A2,1,FIND(",",A2),"")&" "&LEFT(A2,FIND(",",A2)-1))

 

[mmmarks]

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

 

[Rick Rothstein]

I think you need to change your formula to this so that it will not return the name with a leading space in front of it...

=REPLACE(A2,1,FIND(",",A2)+1,"")&" "&LEFT(A2,FIND(",",A2)-1)

Better:

=TRIM(REPLACE(A2,1,FIND(",",A2),"")&" "&LEFT(A2,FIND(",",A2)-1))

Just out of curiosity, why do you consider adding an additional function call to the formula as being better than simply adding one to the result from the existing FIND function?

 

[Aladin Akyurek]

Just out of curiosity, why do you consider adding an additional function call to the formula as being better than simply adding one to the result from the existing FIND function?

Is this really worth discussing for there can be 0 to N spaces around the parts of the names and/or nothing after the comma? The +1 bit after FIND within REPLACE won't work as you may guess.