cancel
Showing results for 
Search instead for 
Did you mean: 

Formula to Find "Bob And Mary Smith" and "Steve and Lisa Jones"

Roger_Coville
Enthusiast

@Hydey_Validity I'm onto another Contact change where I want to find "Bob And Mary Smith".  I find these using your % And %.  I also find "Steve and Lisa Jones"(lower case "a"nd).  When I later grab all the characters before the "And" and after the "And" I get good results. AllTextAfterChar(${FirstName}, "And ")). The issue I'm having is it does not "see" the lower case a and so my forula does not grab the second name.  I have been searching for documentation on %, but no luck.  Is there another way to find only the Large A  And?  Thanks 

8 REPLIES 8

Hydey_Validity
Validity Team Member
Validity Team Member

thanks Roger, fun topic for me to look into tomorrow and definitely one for the wider community. 
I'll have a play tomorrow am and let you know what I find. 

@Hydey_Validity  Where can I find the documentation on % or _ ?  No luck searching the doc for these.

@Roger_Coville Here is a document that reviews % and _ in record selection criteria.  

Thank you @Ashley_Validity - This needs clearer "Billing" in the documentation!

@Roger_Coville , I think I have a solution.  Try this as your modify function:  Should find both 'and' & 'And' and return all the letters to the right of the 'and'

IFERROR(RIGHT(${FirstName},LEN(${FirstName})-FIND(" and",${FirstName},1)-3),IFERROR(RIGHT(${FirstName},LEN(${FirstName})-FIND(" And",${FirstName},1)-3),${FirstName}))

 

@Roger_Coville , Thanks to my colleague @AnthonyValidity who came up with an alternative which is simpler and may work better.   You can try both and see what works best. 

AllTextAfterChar(RegExReplace(${FirstName}, "and ", "And "), "And ")

@Hydey_Validity  @AnthonyValidity   This is great solution - worked as expected and clearer t understand by the next person into my scenario.  Should I post this as a unique question to get this into other hands easier? 

Hey @Roger_Coville ,

I moved part of thread to its own post for you!  You can mark the solutions for the next person that needs them.  Happy to hear they worked!

Anthony Lardiere Jr
Senior Customer Success Manager