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.