15

In cell A1 I have entered "Apple". In B2 I enter the formula =FIND("Apple",A:A). However I keep getting #VALUE error. Can anyone explain this and how do I overcome it?

6
  • Have a look at Finding data in an Excel Table.
    – rickhg12hs
    Nov 11, 2013 at 12:55
  • 1
    Please explain what you are trying to accomplish so we can help you. FIND is used to locate a string of text within a given set of text, then returns the position of of it within the text string. You may be using the wrong function, depending what you are trying to do.
    – CharlieRB
    Nov 11, 2013 at 12:57
  • I am basically trying to search for a text string within a column. The position of the text within the column is not fixed. Should I be using any other function and where am I going wrong with FIND?
    – Gh0sT
    Nov 11, 2013 at 13:07
  • The FIND function works if I were to instead enter the formula in cell B1...strange!
    – Gh0sT
    Nov 11, 2013 at 13:17
  • 1
    Normally you apply FIND to a single cell - if you use =FIND("Apple",A:A) in B2 excel actually returns an "array" of values....but the one you see in the cell will be the result from the column A cell on the same row, so if A2 doesn't contain "Apple" you get #VALUE!, but in B1 you get a number - still best to use a single cell..... Nov 11, 2013 at 21:57

3 Answers 3

5

If you want to find the first cell (row) in Column A that contains the word “apple”, possibly as part of a larger word (e.g., “crabapple” or “applesauce”) or a sentence, use

=MATCH("*apple*", A:A, 0)
1
  • And since this answers the part what I should use instead...I'm marking this as the answer...thank you!
    – Gh0sT
    Nov 12, 2013 at 5:54
27

Find looks in a cell, not a range of cells. The syntax component within_text is looking at a particular string, but a range of cells is not a string. You may be able to achive this with an array, but the simplest approach would be to create a finder column; make a column next to the column you want to search and add the formula in each adjacent cell, so in B2 the within_text statement would be A2.

=FIND("Apple",A1)


I get the feeling you're trying to find the number of instances of the word Apple. If this is correct you could instead use this formula:

=COUNTIF(A:A,"Apple")

But this will only give you a count of all instance of the word Apple in the column A, where Apple is the entire contents of that cell.


If you want to count all instances where a cell contains Apple, even if it also contains other words (i.e Apple Pie) then you'll need to go back to your FIND statement in an extra column and use an IF statement, maybe something like this:

=IF(ISERROR(FIND("Apple",$A2)),0,1)

This will return a 0 if the FIND statement results in an error, and otherwise will return a 1. You can then simply sum that column to get the count of cells with the word Apple somewhere within them.

1
  • Thanks for your explanation...this answers the part where I was going wrong with FIND.
    – Gh0sT
    Nov 12, 2013 at 5:53
0

Parsing values in a cell using the Find function and overcoming the #VALUE! result

Picking up value before the find of "&"

=IF(IFERROR(FIND("&",B:B),0)>0,MID(B:B,1,FIND("&",B:B)-1),TRIM(B:B))

Picking up value after the find of "&"

=IF(IFERROR(FIND("&",B:B),0)>0,MID(B:B,FIND("&",B:B)+1,99),"")

Explanation

  1. Test for error FIND and substitute with 0
  2. Test using outside If
  3. Resolve if results

Example: 1

=IF(IFERROR(FIND("&",B:B),0)>0,MID(B:B,FIND("&",B:B)+1,99),"")
  • Cell contains Mary & David
  • Value becomes 6 for the find
  • Starting at position to right pick up value
  • Result is "David"

Example: 2

  • Cell contains Mary
  • Value becomes 0
  • Result is null

Example: 3

Picking up value before the "&"

=IF(IFERROR(FIND("&",B:B),0)>0,MID(B:B,1,FIND("&",B:B)-1),TRIM(B:B))
  • Cell contains Mary & David
  • Result is Mary
4
  • 1
    Can you add some context to your answer? It isn't very clear on how it solves the original question.
    – Burgi
    Feb 9, 2016 at 9:24
  • @Burgi — On the contrary, it's very clear. Paul has overcome the Find() function's frustrating habit of returning an error if the search text doesn't exist in the searched string. This is a valuable answer—you should upvote it, as I have just now, instead of downvoting it. (It also answer's OP's original question, albeit in a different way.)
    – InteXX
    Aug 18, 2017 at 5:45
  • @InteXX if you note the comment was made in Feb 2016 and there was an edit made shortly afterwards. If you think my comment was no longer necessary you should flag it.
    – Burgi
    Aug 18, 2017 at 8:11
  • @Burgi — That was a pretty good edit. And pardon me... I spoke out of turn in stating that you "should" do one thing or another. I also assumed that you were the downvoter, which was improper as well. If you'll note the statement in my profile, you'll see that downvoting—and flagging—are things in which I choose to not participate. But thank you for your gracious offer.
    – InteXX
    Aug 18, 2017 at 20:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.