FIND function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jeff Klein - 15 Dec 2007 21:11 GMT

I am using the "find" function to locate a particular letter in a cell.
This works fine except when the letter does not exist in the cell.
Displayed in the cell is "#VALUE!" and is not useable.

Is there a way to
=if(find("G",A1,1) = "nothing found",A1,find("G",A1,1))

Reply to this Message

Dave Peterson - 15 Dec 2007 21:22 GMT

=if(iserror(find("G",a1,1)),"nothing found",find("G",a1,1))

will return either the string "nothing found" or the position of the first G in
A1.

You could also use =isnumber()

=if(isnumber(find(...)),"found it","not found")

> I am using the "find" function to locate a particular letter in a cell.
> This works fine except when the letter does not exist in the cell.
> Displayed in the cell is "#VALUE!" and is not useable.
>
> Is there a way to
> =if(find("G",A1,1) = "nothing found",A1,find("G",A1,1))

Signature

Dave Peterson

Reply to this Message

T. Valko - 16 Dec 2007 03:41 GMT

> Is there a way to
> =if(find("G",A1,1) = "nothing found",A1,find("G",A1,1))

Another one:

I assume you are looking specifically for the uppercase "G".

=IF(COUNT(FIND("G",A1)),FIND("G",A1),"Nothing Found")

I see that you're using the [start_num] argument to FIND. If you want to
look at the *entire* string you can just omit the [start_num] argument. If
you're using it thinking it *only* looks at the first character of the
string and that's what you want:

=IF(EXACT(LEFT(A1),"G"),1,"Nothing Found")

Signature

Biff
Microsoft Excel MVP

>I am using the "find" function to locate a particular letter in a cell.
>This works fine except when the letter does not exist in the cell.
>Displayed in the cell is "#VALUE!" and is not useable.
>
> Is there a way to
> =if(find("G",A1,1) = "nothing found",A1,find("G",A1,1))

Reply to this Message

Rate this thread: