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 t...
Let's say that the data are in A1:A4.
=SUMPRODUCT(LEN(A1:A4)-LEN(SUBSTITUTE(A1:A4,"apple","")))/LEN("apple")
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.
- Gotcha, thanks!
