test text string for a comma (,)

[JR Hester]

Excel XP on WinXP

Inherited a spreadsheet from another user. Have a mixture of lname, fname
and fname lname combinations. I can successfully convert the lname, fname to
fname lname, teh problem is those cells that are already in the fname lname
return a #Value error.

I thought I could find a way to logically test the string for a comma(,) and
use an If statement to control my concatenate formula. I can't find a way to
test logically for the comma.

Can anyone offer a solution?

Thank you

 

[JR Hester]

Thanks for allowing me to post the problem. Finished teh post and searched
one more time, came up with teh ISERR and applied that. Worked great! here is
what I finally used
=IF(ISERR(RIGHT(A61,(LEN(A61)-FIND(",",A61)-1))&"
"&LEFT(A61,FIND(",",A61)-1)),A61,RIGHT(A61,(LEN(A61)-FIND(",",A61)-1))&"
"&LEFT(A61,FIND(",",A61)-1))

Where Col A contained a combination of "fname lname" and "lname, fname"
entries.
Maybe this will help someone else down the road!

 

[Dave Peterson]

=if(isnumber(search(a1&" ",",")),"has comma", "no comma")
(a1&" " will avoid "has comma" when A1 is empty)

=if(countif(a1,"*,*")>0,"has comma","no comma")
is another way.

=len(a1)-len(substitute(a1,",",""))
will return the number of commas in A1

 

[T. Valko]

Try this:

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

Lname, Fname will be reversed to Fname Lname

Fname Lname will not change

I didn't test this on names with middle intials or with multiple first and
last names:

Billy Bob B. Jones Jr
De La Hoya, Oscar Jr

If you have names like that....good luck!