1

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.

GeorgeC
  • 956
  • 5
  • 16
  • 40

4 Answers4

3

This can easily be done using the LEN and SUBSTITUTE functions.

=LEN(A2)-LEN(SUBSTITUTE(A2, "^", ""))+1
norie
  • 9,609
  • 2
  • 11
  • 18
2

use:

=INDEX(IF(A1:A="",,LEN(REGEXREPLACE(""&A1:A, "[0-9]", ))+1))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
2

I think I have shorter/simpler than the other solutions.

=counta(split(A1,"^"))-1

There are a few limitations (like it only works with single characters and also requires the string to never end or start with that character).

But it works for the 4 examples in the question.

stallingOne
  • 3,633
  • 3
  • 41
  • 63
1

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,),)})

enter image description here

Or just the count:

=arrayformula(if(A1:A<>"",iferror(len(regexreplace(A1:A,"[^\^]",""))+1,),))

Aresvik
  • 4,484
  • 1
  • 5
  • 18
  • Works well but when I modify it to fit in my structure as `=arrayformula({M2:M,if(M2:M<>"",iferror(len(M2:M)-len(regexreplace(M2:M,"\^","")),)+1,)})` I get the values in column M repeated before the sum value is put into the next column. Apart from hiding this column is there any way to get it not to show? – GeorgeC May 29 '21 at 12:23
  • 1
    If you only want col2, you can remove `M2:M,` from within the `{}`: `=arrayformula({if(M2:M<>"",iferror(len(M2:M)-len(regexreplace(M2:M,"\^","")),)+1,)})` – Aresvik May 29 '21 at 12:35