Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Networkdays function doesn't find true difference between two dat | Excel Worksheet Functions | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
Improve Excel Help Text - Make easier to Find Function Refs | Excel Worksheet Functions | |||
Function that Returns address of that cell? | Excel Worksheet Functions | |||
UDF and Calculation tree | Links and Linking in Excel |