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