Common substring

[bsandt22]

Hey all,

First post here!

My issue is that I'm trying to find the most common substring in a list of strings.

So if my data set was:

1. AMZN MKTP US*M06TK6Y01 AMZN.C AMZN MKTP US*M06TK6Y01
2. Amazon.com*M28R03BA0 Amzn.c Amazon.com*M28R03BA0
3. Amazon.com*M255Q4E62 Amzn.c Amazon.com*M255Q4E62
4. Amazon.com*MB5OT5BL0

It would return amzn.c

And if my set was

1. ADOBE *ACROPRO SUBS 800833
2. ADOBE *ACROPRO SUBS 800833 ADOBE *ACROPRO SUBS
3. ADOBE *ACROPRO SUBS 800833 ADOBE *ACROPRO SUBS
4. ADOBE *ACROPRO SUBS

It would return ADOBE *ACROPRO SUBS


I've used a couple different Macros that can pull a common substrings from 2 cells but can't seem to have it pull from an entire array.

Been working on this forever and cannot for the life of me figure it out...

[6StringJazzer]

In your first example, why isn't the most common substring "Amazon.com*M"? It occurs 5 times. AMZN.C only occurs 3 times.

Does a substring have to be a whole "word" (i.e., delimited by spaces) or can it be embedded in other strings?

This is quite a difficult problem, by the way.

[bsandt22]

That's my mistake! I forgot to add the amzn.c to the 4th line. You are correct though that amzn.com would still appear less times than amazon.com*m.

In this case I would be more interested in the string that appears in each individual line most often, rather than the string that appears the most times in the entire array.

Not sure I explained it that well, as an example the first array I provided (given the addition of amzn.c at the end), would have 4 unique lines that contained "amzn.c" but only 3 unqiue lines that contain amazon.com*. Regardless, either option would probably work in this case.

The substring can absolutely be embedded within other strings. If you'd like an example of the application of this I'd be happy to provide more information.

Its something that has stumped me for a long time which is why I reached out here :/

Thanks for any help here!