Help searching an array of text strings for common strings

I've been having some difficulties using a combination of index, countif, and match. What I am trying to do is reference text strings from multiple cells, and return the most commonly used phrase. I am pretty sure VBA would be much more suited to the particular task, but I am not familiar with VBA scripting.

Suppose A1:A10

orange
I think its Orange
Definite yellow
red
Looks orAnge
probably blue
its purple, duh
iTs Å orange
red
Appears to be ORANGE

The array formula I have so far is:
=INDEX(A1:A10,MATCH(MAX(COUNTIF(A1:A10,A1:A10)),COUNTIF(A1:A10,A1:A10),0))

which returns 'red' - since red is the most common text string. But I want the formula to actually look into the text strings and return the value 'orange'.

Eventually I'd like to be able to specify how many characters in the phrase to search for. Currently its just referencing a whole word.

Any ideas or help would be great.

Hi ABComp,

Try adding wild cards to your COUNTIF criteria as follows:

=INDEX(A1:A10,MATCH(MAX(COUNTIF(A1:A10,"*"&A1:A10&"*")),COUNTIF(A1:A10,"*"&A1:A10&"*"),0))

Here's VBA (UDF) solution
Use in cell like

=MostCommonWord(A1:A10)

It will extract multiple words when it finds multiple most common words in the range.

To a standard module

Please Login or Register  to view this content.