SOLVED

Find text value in a column that contains ";"

%3CLINGO-SUB%20id%3D%22lingo-sub-2516259%22%20slang%3D%22en-US%22%3EFind%20text%20value%20in%20a%20column%20that%20contains%20%22%3B%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2516259%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETrying%20to%20count%20how%20many%20times%20the%20word%20%22apple%22%20appears%20in%20a%20column%20where%20the%20word%20%22apple%22%20can%20be%20separated%20by%20%22%2C%22%20or%20%22%3B%22%3F%20Example%20see%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20value%20I%20expect%20is%20that%20the%20count%20of%20the%20word%20%22apple%22%20should%20be%20%3D%205.%20Thank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22113%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22113%22%3Eapple%3B%20apple%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eapple%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3Epear%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3Eapple%2C%20apple%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2516259%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2516311%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20text%20value%20in%20a%20column%20that%20contains%20%22%3B%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2516311%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1095886%22%20target%3D%22_blank%22%3E%40mingjzhu%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20that%20the%20data%20are%20in%20A1%3AA4.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMPRODUCT(LEN(A1%3AA4)-LEN(SUBSTITUTE(A1%3AA4%2C%22apple%22%2C%22%22)))%2FLEN(%22apple%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2516314%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20text%20value%20in%20a%20column%20that%20contains%20%22%3B%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2516314%22%20slang%3D%22en-US%22%3EThanks%20so%20much%2C%20Hans!%20It%20worked!%3CBR%20%2F%3E%3CBR%20%2F%3Ebtw%2C%20i%20tried%20to%20use%20%3Dcount(search).%20Easier%20formula%20but%20it%20always%20seems%20to%20be%20off.%20Not%20sure%20why%3F%3C%2FLINGO-BODY%3E
New 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 (New 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!