Search code examples
google-sheetsgoogle-sheets-formula

Google sheets: Count Specific Characters in a string of words

I have on Google sheets this data (It is the same principle in excel).

I want to count the numbers of dose in each strings.

I use this formula and it return me this:

=LEN(L3)-LEN(SUBSTITUTE(L3,"/",""))
Product dose Count "/"
1 0
2 5G 0
3 10G/20G/24g 2

I would like instead in returns me this :

Product dose Count "/"
1
2 5G 1
3 10G/20G/24g 2

How I can modify my formula to return me this?

Solution

  • In C2:

    =ARRAYFORMULA(IF(LEN(B2:B),LEN(B2:B)-LEN(SUBSTITUTE(B2:B,"/",""))+1,""))

    Or:

    =ARRAYFORMULA(IF(LEN(B2:B),LEN(REGEXREPLACE(B2:B,"[^/]",""))+1,""))