Find function returns the #VALUE! error value

[Ken]

Hi, Using Excel 2003 with no VBA.

I am using the Find function in a cell to search for a comma. When it finds
the comma no problem but the function returns the #VALUE if it does not find
the comma. How do I trap for the error?

I am looking at a cell A1 for names and putting the answer in B1. If the
name is "last, first" I switch them to "first last" with a space. If A1
contains "first last" with no comma I am leaving them alone. Cell Function
is:

=IF(FIND(",",A1,1),A1,RIGHT(A1,LEN(A1)-SEARCH(",",A1,1))&" "&
LEFT(A1,SEARCH(",",A1,1)-1))

Works with comma but returns #VALUE! with no comma.

How to trap for error?

Ken

 

[Ken]

I found it.

=IF(ISERROR(FIND(",",A1,1)),A1,RIGHT(A1,LEN(A1)-SEARCH(",",A1,1))&" "&
LEFT(A1,SEARCH(",",A1,1)-1))

Ken

 

[Ron Rosenfeld]

Hi, Using Excel 2003 with no VBA.

I am using the Find function in a cell to search for a comma. When it finds
the comma no problem but the function returns the #VALUE if it does not find
the comma. How do I trap for the error?

I am looking at a cell A1 for names and putting the answer in B1. If the
name is "last, first" I switch them to "first last" with a space. If A1
contains "first last" with no comma I am leaving them alone. Cell Function
is:

=IF(FIND(",",A1,1),A1,RIGHT(A1,LEN(A1)-SEARCH(",",A1,1))&" "&
LEFT(A1,SEARCH(",",A1,1)-1))

Works with comma but returns #VALUE! with no comma.

How to trap for error?

Ken

Try this:

=IF(COUNTIF(A1,"*,*"),MID(A1,FIND(", ",A1)+2,255)
&" "&LEFT(A1,FIND(",",A1)-1),A1)


--ron