# Thread: Using the "Find" function

1. Registered User
Join Date
Mar 2004
Posts
11

## Unanswered: Using the "Find" function

I'm not usually very good at explaining what I'm trying to achieve, but I'll have a go! If you would like me to post an example, please ask.

I'm looking to use the 'Find' function to see whether or not a set of characters is present in a cell. It should return a number greater than 0 if they are present. However I noticed in the Help article on this function that if the set of characters is not present, the formula will always return the #VALUE! error. Is there any way of getting round this so that it gives me a 'proper' (ie. can be used in other formulas) result if the characters are not present?

beki

2. Registered User
Join Date
Jan 2004
Location
Aberdeen, Scotland
Posts
1,067
Hi Beki

Try Something like this

=IF(ISERROR(FIND(A1,B1)),"Something",FIND(A1,B1))

HTH

David

3. Registered User
Join Date
Mar 2004
Posts
11
IF((LEN(A35)<=8),"X",IF(AND(LEN(A35)=12, IF(ISERROR(FIND("00P",A35,1)), "", find("00P", A35, 1))>1 "X", "")

Can you spot the error in this? I've tried to insert the formula and can't see where it's going wrong...

Thanks!
B

4. Registered User
Join Date
Oct 2003
Posts
1,091
Originally Posted by bekibutton
IF((LEN(A35)<=8),"X",IF(AND(LEN(A35)=12, IF(ISERROR(FIND("00P",A35,1)), "", find("00P", A35, 1))>1 "X", "")

Can you spot the error in this? I've tried to insert the formula and can't see where it's going wrong...

Thanks!
B
Looks like you have some extra spaces, and a missing parens. Try something like this:

=IF((LEN(A35)<=8),"X",IF(AND(LEN(A35)=12,IF(ISERRO R(FIND("00P",A35,1)),"",FIND("00P",A35,1))>1),"X", ""))

Not sure if the combination is OOP (letters) or 00P (two numbers one letter).

5. Registered User
Join Date
Mar 2004
Posts
11
I had a mess around with spaces and parantheses etc and it worked eventually! Thanks for your help anyway.

I have another question. Is there any way of using the formula to find a range of characters? For example if I wanted to know whether a string ended with text or a number (1-9)?

Thanks
Beki

6. Registered User
Join Date
Jan 2004
Location
Aberdeen, Scotland
Posts
1,067
Hi Beki

I do this by this Function

=IF(ISNUMBER(VALUE(RIGHT(A2,1))),"Number","False")

The RIGHT function picks up the last character of text
Value changes text to numbers
ISNUMBER detrmines whether or not something is a number

Hope this Helps
David

7. Registered User
Join Date
Mar 2004
Posts
11
It does, thanks. Unfortunately I've run out of room for nesting functions in my formula! I guess I could do a supplementary one though.

Beki

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•