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

1. ## 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. ## 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. ## Re: Checking if the cell ends with a certain string of characters.

They vary in length.
As an example: .aaa
.aarp
.abb
.abbott
.ac
.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.

4. ## 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

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

#### 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