Find most common letter in string

[SteveWright]

Hi guys. I'm looking to write a formula (not macro if possible) that looks for the most common letter in a string.

Lets suppose cell A1 = aaabb then the formula would return "a".

I've been messing around with code() and mode() but cant seem to write one formula to complete this job.

I have a UDF allready written, but its quite slow.

Many thanks,

 

[Domenic]

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=CHAR(MODE(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))

Note that if there's two or more letters tied for the most common letter, the formula returns the first letter that occurs in the text string.

 

[SteveWright]

Genius,

Thanks.

 

[Imgonna]

Genius,

Thanks.

<< Exactly what I'm looking for - and it works great.

I added in SUBSTITUTE(A1," ","") to remove space in A1 to allow me to add sentences.

Being a theorist - anyone have a stab at explain how the formula works?

Cheers
Glen

 

[pgc01]

Another option, for a Unicode character:

=MID(A1,MATCH(MAX(LEN(A1)-LEN(SUBSTITUTE(A1,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),""))),LEN(A1)-LEN(SUBSTITUTE(A1,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"")),0),1)

... confirmed with CSE

In case of tie returns the first one.