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?
3 Answers
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)
answered Nov 12, 2013 at 1:45
-
And since this answers the part what I should use instead...I'm marking this as the answer...thank you!– Gh0sTNov 12, 2013 at 5:54
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.
answered Nov 11, 2013 at 13:32
-
Thanks for your explanation...this answers the part where I was going wrong with FIND.– Gh0sTNov 12, 2013 at 5:53
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
- Test for error FIND and substitute with 0
- Test using outside If
- 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
-
1Can you add some context to your answer? It isn't very clear on how it solves the original question.– BurgiFeb 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.)– InteXXAug 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.– BurgiAug 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.– InteXXAug 18, 2017 at 20:26
FINDis 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.FIND?FINDto a single cell - if you use=FIND("Apple",A:A)inB2excel 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.....