I have an Excel file containing a piece of HTML code in a cell:
<li>item 1</li><li>item 2</li><li>item 3</li>
Now I want to count the occurrences of the string "<li>" in the cell. I searched on the web and used the fairly popular formula:
=LEN(A2)-LEN(SUBSTITUTE(A2,"<li>",""))
The result of which I am hoping for should obviously be 3 but for some unknown reason, it gives 12:
How do I get it to work as intended?
22 Answers
It is giving 12, as it counts <li> as a 4, as that is its length, so to fix this simply use:
=LEN(A2)-LEN(SUBSTITUTE(A2,"<li>",""))/4
Or i prefer to use SUMPRODUCT :
=SUMPRODUCT(LEN(A2)-LEN(SUBSTITUTE(A2,"<li>","")))/4You need to divide by the length of the substituted string:
=(LEN(A2)-LEN(SUBSTITUTE(A2,"<li>","")))/4
