+ Reply to Thread
Results 1 to 5 of 5

Check if string ends with one of the strings from an array

  1. #1
    Registered User
    Join Date
    09-28-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    8

    Check if string ends with one of the strings from an array

    Hey,

    Here's what I'm trying to do: basically I have a list of subdomains from which I want to extract the root domain.

    Up until now I've used the following steps:
    1. Find the last "." occurence and retrieve what's to the right of it (basically the TLD): "something.example.com" would retrieve ".com"
    2. Repeat step 1 for the string without the result in step 1: "something.example" would retrieve "example"
    3. Concatenate step 2 and step 1 to get root domain: "example.com"

    Here's the problem I've encountered using this method (it's in step 1): trying to retrieve root domain for "something.example.co.uk" would only retrieve ".co.uk"

    I'm not sure if there's a quick way to fix that, other than actually use a list of possible TLDs (including second level), and see if my string ends in one of the TLDs from the list.

    Here's an example:

    Subdomain Result TLD List
    something.example.com .com .com
    abc.letters.co.uk .co.uk .org
    www.comparison.org .org .net
    .co.uk
    .etc

    I wanna put emphasis that the string must end and not just contain the tld, since for the third example, it might retrieve ".com" from ".comparison" instead of ".org".

    So, any ideas on what I can use to solve this (if there's any way I can avoid using a TLD list, that'd be best)?

    Cheers!

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Check if string ends with one of the strings from an array

    Hi alienss,


    trying to retrieve root domain for "something.example.co.uk" would only retrieve ".co.uk"
    would it be simple extraction of .co.uk from right side of the string ?


    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Check if string ends with one of the strings from an array

    something like this maybe
    Attached Files Attached Files
    Last edited by martindwilson; 03-15-2013 at 02:15 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Check if string ends with one of the strings from an array

    hmm problem with upload try again oh blast now its doing it ignore this

  5. #5
    Registered User
    Join Date
    09-28-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Check if string ends with one of the strings from an array

    Martin yes! That seems to do it. Does the "2^15" lookup value means that the formula will always grab the last occurrence?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1