I would like to count the number of occurances of a string within a single cell in Google Sheets.
If I have
599^612
600
601^602^604
44^56^71^83^95^107^119^131^402^451^466^478^490^502^514^571^633^648^661^680^746^813^878^945^1010^1069^1100^1131^1161^1226^1247^1258
I would like to get the counts of ^ in each row.
Cell | Count
599^612 | 2
600 | 1
601^602^604 | 3
44^56^71^83^95^107^119^131^402 | 9
I have tried find, count and countif but can't work it out. Also reviewed Count the number occurrences of a character in a string but need a solution in Google Sheets.
I'm not sure why 44^56^71^83^95^107^119^131^402^451^466^478^490^502^514^571^633^648^661^680^746^813^878^945^1010^1069^1100^1131^1161^1226^1247^1258
cuts off at 44^56^71^83^95^107^119^131^402
, but you could count ^
like this:
=arrayformula({A1:A,if(A1:A<>"",iferror(len(regexreplace(A1:A,"[^\^]",""))+1,),)})
Or just the count:
=arrayformula(if(A1:A<>"",iferror(len(regexreplace(A1:A,"[^\^]",""))+1,),))