CODEWITHSUNDEEP
excelstringexcel-formula
Alessandro
Alessandro

Reputation: 794

Count sub-strings in a cell from a range of cells (excel)

Suppose in cells A1, A2, and A3 there are three strings, one for each of them. I would count how many times the strings in A2 and A3 are contained in A1. My solution is:

=(LEN(A1)-LEN(SUBSTITUTE(A1;A2;"")))/LEN(A2) + (LEN(A1)-LEN(SUBSTITUTE(A1;A3;"")))/LEN(A3)

The result is correct, but I need to write the interval A2:A3 not in an explicit way (for example I could be interested in searching in another interval, say X1:X100, and of course I can't write explicitly the sum of 100 addends). Thanks!

Upvotes: 0

Views: 51

Answers (1)

Birgit Vera Schmidt
Birgit Vera Schmidt

Reputation: 391

Excel can deal with vectors fairly nicely:

=SUM((LEN(A1)-LEN(SUBSTITUTE(A1,A2:A100,"")))/LEN(A2:A100))

Upvotes: 2

Related Questions