Find function returns the #VALUE! error value
 

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

Find function returns the #VALUE! error value
 

I found it.

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

Ken

"Ken" wrote in message
...
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

Find function returns the #VALUE! error value
 

On Sat, 28 Oct 2006 18:20:18 -0700, "Ken" wrote:

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