Forum Discussion
Solved
Find text value in a column that contains ";"
Hi All:
Trying to count how many times the word "apple" appears in a column where the word "apple" can be separated by "," or ";"? Example see below.
The value I expect is that the count of the word "apple" should be = 5. Thank you!
| apple; apple |
| apple |
pear |
apple, apple |
Let's say that the data are in A1:A4.
=SUMPRODUCT(LEN(A1:A4)-LEN(SUBSTITUTE(A1:A4,"apple","")))/LEN("apple")
4 Replies
Let's say that the data are in A1:A4.
=SUMPRODUCT(LEN(A1:A4)-LEN(SUBSTITUTE(A1:A4,"apple","")))/LEN("apple")- Thanks so much, Hans! It worked!
btw, i tried to use =count(search). Easier formula but it always seems to be off. Not sure why?The COUNT function returns the number of cells in a range that have a numeric value. It cannot be used to count text values.
You can use =COUNTIF(A1:A4,"*apple*") to count the number of cells that contain the word "apple". But it will count each such cell only once.
