SOLVED

Find text value in a column that contains ";"

Copper Contributor

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

4 Replies
best response confirmed by mingjzhu (Copper Contributor)
Solution

@mingjzhu 

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?

@mingjzhu 

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!
1 best response

Accepted Solutions
best response confirmed by mingjzhu (Copper Contributor)
Solution

@mingjzhu 

Let's say that the data are in A1:A4.

=SUMPRODUCT(LEN(A1:A4)-LEN(SUBSTITUTE(A1:A4,"apple","")))/LEN("apple")

 

View solution in original post