test text string for a comma (,)

  • Thread starter Thread starter JR Hester
  • Start date Start date
J

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
 
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!
 
=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
 
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!
 
Back
Top