+ Reply to Thread
Results 1 to 4 of 4

Checking if the cell ends with a certain string of characters.

  1. #1
    Registered User
    Join Date
    02-02-2016
    Location
    Orono, ME
    MS-Off Ver
    2013
    Posts
    21

    Checking if the cell ends with a certain string of characters.

    Hello, I am trying to write a function to determine if a list of email addresses are valid: Here is my problem:

    The last check is if the lists (starting at B2, ends with a valid extension. .com,.net ETC. I have a list of all 1107 extensions in a different tab workbook.
    Im guessing the length has to be involved somewhere, but am at a complete loss at where to start for this.

    Any help would be greatly appreciated.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,115

    Re: Checking if the cell ends with a certain string of characters.

    Are the valid extensions all the same length (i.e. 4 characters including the full-stop), or do they vary in length? If so, what is the variation?

    Pete

  3. #3
    Registered User
    Join Date
    02-02-2016
    Location
    Orono, ME
    MS-Off Ver
    2013
    Posts
    21

    Re: Checking if the cell ends with a certain string of characters.

    They vary in length.
    As an example: .aaa
    .aarp
    .abb
    .abbott
    .abogado
    .ac
    .academy
    .accenture
    .accountant
    .accountants
    .aco
    .active

    I have already acquired the position of the final period in the email address using =SEARCH("^^",SUBSTITUTE(B2,".","^^",LEN(B2)-LEN(SUBSTITUTE(B2,".",""))))
    So using that, how would i check the my list, starting at B2 to make sure they conform to the proper extensions?

    Using this: =MID(B2,H2,LEN(B2)-H2+1) gives me the extension of the email address, but as far as making sure that is within the list of valid extensions, I have no idea.
    Last edited by loganc123; 04-26-2016 at 09:44 PM.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,115

    Re: Checking if the cell ends with a certain string of characters.

    Assuming your list of valid extensions is in column A of Sheet2, you could use this:

    =IF(COUNTIF(Sheet2!A:A,TRIM(MID(B2,H2,255))),"Valid","No good")

    or this:

    =IF(ISNUMBER(MATCH(TRIM(MID(B2,H2,255)),Sheet2!A:A,0)),"Okay","Rubbish")

    then copy down. These assume that H2 contains the formula to find the final period of the email address in B2, so put the formula in the appropriate column on row 2.

    Hope this helps.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. checking number of characters in a cell macro
    By mehdoush in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-08-2014, 02:13 PM
  2. [SOLVED] Check if string ends with one of the strings from an array
    By alienss in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-15-2013, 02:58 PM
  3. Replies: 4
    Last Post: 01-22-2013, 04:39 PM
  4. Replies: 3
    Last Post: 04-05-2012, 04:58 PM
  5. Flag cells with data that ends with alpha characters
    By rfisher1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-13-2011, 10:29 AM
  6. checking a cell if it contains 6 characters
    By Daehoon in forum Excel General
    Replies: 5
    Last Post: 11-12-2010, 04:30 AM
  7. the ' symbol ends the SQL string, how do you replace it Excel
    By Valori in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-02-2006, 09:50 PM

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