I've been struggling to count how many times multiple different text strings appear in a cell.
In column A, I have a text string with multiple file types, and I'm trying to count how many of each file type occur in that string.
Does any formula count the number of times a text string occurs in a single cell?
Expected Result
Does any formula count the number of times a text string occurs in a single cell?
Not directly, but you can calculate it:
=(LEN(A1) - LEN(SUBSTITUTE(A1,"doc",""))) / LEN("doc")
To change the formula to look for entire words only would make it very complicated with standard Excel formulas.
Here I use free Regex Find/Replace add-in.
With that you can use this formula:
=(LEN(A1)-LEN(RegExReplace(A1,"(\W?)doc(?!\w)","$1")))/LEN("doc")
How it works:
(?!\w) is a negative lookahead, considers only doc when it's not followed by that letter(\W?) - as this add-in doesn't support lookbehind, here I use a group construct, looking for a non-letter before doc(note: I like that add-in and not affiliated with it in any way)
For your specific situation, a formula solution is relatively simple.
Enter the following formula in B2 and ctrl-enter/copy-paste/fill-down/auto-fill into the rest of the table's column:
=(LEN($A2&",")-LEN(SUBSTITUTE($A2&",","/doc,","")))/LEN("/doc,")
Repeat this formula for the other columns, changing the doc to the column's file type. However, for the no. of text column, you need to use plain instead on text.
Explanation:
Adding a comma to the end of the string in column A guarantees that the sub-string we are looking for is always delimited with a / prefix and a , suffix.
The count of the sub-string occurrences is then simply the difference between the length of the full string, and the full string with the delimited sub-strings removed, divided by the length of the delimited sub-string.
A slightly more complicated formula can also be constructed that extracts the file-type to be counted from the header itself, thus only requiring a single formula for the entire table:
Enter the following formula in B2 and ctrl-enter/copy-paste/fill-down&right/auto-fill into the rest of the table:
=(LEN($A2)+1-LEN(SUBSTITUTE($A2&",","/"&MID(B$1,8,LEN(B$1))&",","")))/(LEN(MID(B$1,8,LEN(B$1)))+2)
Note that the header for column H has to be change to no. of plain for this formula to work as per your requirements.
Explanation:
This formula works in essentially the same way as the previous one.
The only major difference is that instead of hard-coding the file type, MID(B$1,8,LEN(B$1)) is used to extract it from the header. This is essentially the equivalent of a two-argument only MID() function that extracts the sub-string from the 8th character to the end of the string. (LEN(B$1) is used instead of an arbitrarily large number as it results in the "cleanest" no-chance-of-a-bug solution.)
The other, minor difference in this formula is a slight simplification achieved by hard-coding the lengths of the delimiters.