Need Help to Run Formula IF Error

[rouse11]

I am currently working on a database that features a "Name" column with names in two formats, both "last, first" and "first last". I have been trying to figure out an automated way to convert all of the "last, first" to "first last", yet skipping over the ones that are already in the correct format. Essentially, I have tried to run the following formula *ONLY* if the cell contains a comma ",".

Formula to switch names from "last, first" to "first last":
=RIGHT(A1,LEN(A1)-LEN(LEFT(A1,FIND(",",A1)

Here is the formula I came up with that is not working completely:
=IF(ISERROR(FIND(",",A1,1))=TRUE,A1,RIGHT(A1,LEN(A1)-LEN(LEFT(A1,FIND(",",A1)))))

I can't quite get the name order switching formula to work when nested in the "ISERROR" format. Any clues on how to get this to work to clean-up the data would be helpful. Thanks.

 

[Blade Hunter]

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

Hunter, Blade in Cell A1 will return as Blade Hunter

 

[Aladin Akyurek]

<TABLE style="WIDTH: 121pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=160 x:str><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 62pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 width=82 align=left>doe, joe</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=78 align=left x:fmla='=IF(A1="","",IF(ISNUMBER(FIND(",",A1)),TRIM(REPLACE(A1,1,FIND(",",A1),""))&" "&LEFT(A1,FIND(",",A1)-1),A1))'>joe doe</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 align=left>jane doe</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=left x:fmla='=IF(A2="","",IF(ISNUMBER(FIND(",",A2)),TRIM(REPLACE(A2,1,FIND(",",A2),""))&" "&LEFT(A2,FIND(",",A2)-1),A2))'>jane doe</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:str="" x:fmla='=IF(A3="","",IF(ISNUMBER(FIND(",",A3)),TRIM(REPLACE(A3,1,FIND(",",A3),""))&" "&LEFT(A3,FIND(",",A3)-1),A3))'> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 align=left>romney, john</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=left x:fmla='=IF(A4="","",IF(ISNUMBER(FIND(",",A4)),TRIM(REPLACE(A4,1,FIND(",",A4),""))&" "&LEFT(A4,FIND(",",A4)-1),A4))'>john romney</TD></TR></TBODY></TABLE>

B2, copy down:

=IF(A1="","",IF(ISNUMBER(FIND(",",A1)),TRIM(REPLACE(A1,1,FIND(",",A1),""))&" "&LEFT(A1,FIND(",",A1)-1),A1))

 

[Blade Hunter]

With the error check:

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

Heh, as usual Aladin's is better

 

[rouse11]

<TABLE style="WIDTH: 121pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=160 x:str><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 62pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 width=82 align=left>doe, joe</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=78 align=left x:fmla='=IF(A1="","",IF(ISNUMBER(FIND(",",A1)),TRIM(REPLACE(A1,1,FIND(",",A1),""))&" "&LEFT(A1,FIND(",",A1)-1),A1))'>joe doe</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 align=left>jane doe</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=left x:fmla='=IF(A2="","",IF(ISNUMBER(FIND(",",A2)),TRIM(REPLACE(A2,1,FIND(",",A2),""))&" "&LEFT(A2,FIND(",",A2)-1),A2))'>jane doe</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:str="" x:fmla='=IF(A3="","",IF(ISNUMBER(FIND(",",A3)),TRIM(REPLACE(A3,1,FIND(",",A3),""))&" "&LEFT(A3,FIND(",",A3)-1),A3))'> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 align=left>romney, john</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=left x:fmla='=IF(A4="","",IF(ISNUMBER(FIND(",",A4)),TRIM(REPLACE(A4,1,FIND(",",A4),""))&" "&LEFT(A4,FIND(",",A4)-1),A4))'>john romney</TD></TR></TBODY></TABLE>

B2, copy down:

=IF(A1="","",IF(ISNUMBER(FIND(",",A1)),TRIM(REPLACE(A1,1,FIND(",",A1),""))&" "&LEFT(A1,FIND(",",A1)-1),A1))

This formula worked like a charm. Thanks!!!