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
|