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?
10
4
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)
-
And since this answers the part what I should use instead...I'm marking this as the answer...thank you! – Gh0sT Nov 12 '13 at 5:54
17
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.
-
Thanks for your explanation...this answers the part where I was going wrong with FIND. – Gh0sT Nov 12 '13 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
- 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. – Burgi Feb 9 '16 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 '17 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 '17 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 '17 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. – CharlieRB Nov 11 '13 at 12:57FIND? – Gh0sT Nov 11 '13 at 13:07FINDto 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..... – barry houdini Nov 11 '13 at 21:57