Results 1 to 7 of 7
  1. #1
    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?

    Thanks for your help,
    beki

  2. #2
    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. #3
    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. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Quote 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).
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    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. #6
    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. #7
    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
  •