Dynamic LEFT function help

Hi there,

New to the forum, hopefully someone has experience with this and will be able to help. I have a list of about 10,000 websites all ending in different domains (com, org, biz, fr, cc, etc, etc), I also have a list of every domain ending possible. My question is how to adapt this left function which is used to remove everything after .com (ex: www.something.com/something to www.something.com) LEFT(D2,FIND("com",D2)+2) such that instead of using the hardcoded 'com' it can simply reference the table I have with all domain endings?

Thanks in advance

Max

If you have a list of all your 'www.somethings.com' in say G1:G20 for example
And A1 is the whole string

Try
=LOOKUP(2^15,SEARCH($G$1:$G$20,A1),$G$1:$G$20)


Note, there cannot be any blanks in your list of somethings.com (G1:G20)

I went with something like this:

=LEFT(A1,SEARCH(INDEX($P$1:$P$5,MAX(IF(ISERROR(FIND($P$1:$P$5,A1)),-1,1)*(ROW($P$1:$P$5)-ROW($P$1)+1))),A1)+2)

With your list of endings in P1:P5. See attached for details. Tony raises a good point, though.