test text string for a comma (,)
 

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

test text string for a comma (,)
 

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!
"JR Hester" wrote:

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

test text string for a comma (,)
 

=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


JR Hester wrote:

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

--

Dave Peterson

test text string for a comma (,)
 

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!

--
Biff
Microsoft Excel MVP


"JR Hester" wrote in message
...
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!
"JR Hester" wrote:

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