function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion

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. is found to be "contained" in the serialized list when is in the list.  I thought maybe I could use a REGEX to limit the match to the whole email address ( 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.


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.





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.



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. ;)