Most ocuring chatacter in a cell-CodePudding

What's the formula to find which is the most frequent character in a cell in Google Sheets? E.G. In cell N8 it says: "What's your name?". I'd like the formula to return "a", as it is the most frequent character in N8.

CodePudding user response：

you can get the total distribution like this:

=QUERY(FLATTEN(REGEXEXTRACT(A1, REPT("(.)", LEN(A1)))), 
 "select Col1,count(Col1) group by Col1 order by count(Col1) desc label count(Col1)''")

notice that two empty spaces are counted as well

if you want to skip them use:

=QUERY(FLATTEN(REGEXEXTRACT(A1, REPT("(.)", LEN(A1)))), 
 "select Col1,count(Col1) where Col1 <> ' ' group by Col1 
  order by count(Col1) desc label count(Col1)''")

also, keep in mind that this is case-sensitive:

to make it case-insensitive use:

=QUERY(FLATTEN(REGEXEXTRACT(LOWER(A1), REPT("(.)", LEN(A1)))), 
 "select Col1,count(Col1) where Col1 <> ' ' group by Col1 
  order by count(Col1) desc label count(Col1)''")

so to get the top value only use:

=INDEX(QUERY(FLATTEN(REGEXEXTRACT(LOWER(A1), REPT("(.)", LEN(A1)))), 
 "select Col1,count(Col1) where Col1 <> ' ' group by Col1 
  order by count(Col1) desc label count(Col1)''"), 1, 1)

CodePudding user response：

This should work:

=index(Sort(filter({LEN(substitute($A$1,mid($A$1,ROw(A:A),1),"")),mid($A$1,ROw(A:A),1)},Row(A:A)<= LEN(A1)),1,true),1,2)

I'm not sure what you'd expect for when there's a tie, but it would consistently return the character most used. If you wanted to skip spaces, you could do this...

=index(Sort(filter({LEN(substitute($A$1,mid(substitute($A$1," 
",""),ROw(A:A),1),"")),mid(substitute($A$1," ",""),ROw(A:A),1)},Row(A:A) 
<= LEN(substitute(A1," ",""))),1,true),1,2)