Special count (Alt + Enter)

[hesam bahrani]

Hi,

We want to count down a series of duplicate words, now you are in a cell with a single word of all and with ALT + enter separating
How do we count them?

For Example in this column count (A1:A5 ) of "Hesam" is 6

	A
1	Hesam
2	Ronaldo Hesam
3	Hesam Hesam
4	Hesam Ronaldo
5	Hesam

<tbody>
</tbody>

Thank you!

 

[Norie]

Probably a better way but you could try this array formula.

=SUM(LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,"Hesam","")))/LEN("Hesam")

In fact if your version of Excel has the TEXTJOIN function you could use this non-array formula.

=(LEN(TEXTJOIN("",FALSE,A1:A5))-LEN(SUBSTITUTE(TEXTJOIN("",FALSE,A1:A5), "Hesam", "")))/LEN("Hesam")

 

[steve the fish]

You could use sumproduct. Still array but doesnt need the CSE:

=SUMPRODUCT(LEN(A1:A5)-LEN(SUBSTITUTE(PROPER(A1:A5),"Hesam","")))/LEN("Hesam")

 

[hesam bahrani]

Probably a better way but you could try this array formula.

=SUM(LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,"Hesam","")))/LEN("Hesam")

In fact if your version of Excel has the TEXTJOIN function you could use this non-array formula.

=(LEN(TEXTJOIN("",FALSE,A1:A5))-LEN(SUBSTITUTE(TEXTJOIN("",FALSE,A1:A5), "Hesam", "")))/LEN("Hesam")

Thank you!!

 

[hesam bahrani]

You could use sumproduct. Still array but doesnt need the CSE:

=SUMPRODUCT(LEN(A1:A5)-LEN(SUBSTITUTE(PROPER(A1:A5),"Hesam","")))/LEN("Hesam")

very good!!