Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
thunksalotthunksalot 

Finding if a serialized list contains a *whole* email address (and only a whole email address)

I'm trying to use a formula to see if an email address contained in one field is contained in a serialized list of email addresses contained in another field.  The problem I'm running into is that using CONTAINS() results in false matches when the search email address is found within a part of a whole email address in the list.  E.g. guy@hotmail.com is found to be "contained" in the serialized list when awesomeguy@hotmail.com is in the list.  I thought maybe I could use a REGEX to limit the match to the whole email address (guy@hotmail.com) surrounded by white space or a comma but couldn't figure out how to do that.

 

Right now my formula is:

CONTAINS( Obsolete_Emails__c , Email )

 

Any advice?  I suppose I could do what I'm trying to do with a trigger, but I'm so close to having it working this way.  Thanks for your help.

Ispita_NavatarIspita_Navatar

Please try the following :-

 

 

IF(FIND(search_text, text , 0) ,  IF(OR(MID(search_text, FIND(search_text, text , 0), 1) = " " , MID(search_text, FIND(search_text, text , 0), 1) = ","), "Contains whole email" ,"Does not have whole email"), "Does not have email" )

 

 

Did this answer your question? If not, let me know what didn't work, or if so, please mark it solved.

 

 

 
thunksalotthunksalot

Thanks so much for the suggested solution.  When I tried it I got this error:

 

Error: Incorrect parameter for function 'IF()'. Expected Boolean, received Number

 

Here's what I put in as the formula:

 

IF(FIND(Obsolete_Email__c, npe01__AlternateEmail__c , 0) ,  IF(OR(MID(Obsolete_Email__c, FIND(Obsolete_Email__c, npe01__AlternateEmail__c , 0), 1) = " " , MID(Obsolete_Email__c, FIND(Obsolete_Email__c, npe01__AlternateEmail__c , 0), 1) = ","), TRUE , FALSE), FALSE )

 Seems like FIND is returning an index, which I think is correct, and SF can't evaluate that as a boolean.

 
thunksalotthunksalot

Would their be a way to find the searchtext in the text, and then - using its starting position in the text and its length - grab the character before and after it in order to test them with regex to make sure they are either NULL, \s or ","?  Just a crazy idea I thought of after *trying* to understand what you suggested.  Wouldn't have the slightest idea how to implement it. ;)