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?

Thanks for your help,
beki

Hi Beki

Try Something like this

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

HTH

David

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).

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

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

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