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

[Roger_Coville]

@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 

[Hydey_Validity]

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. 

[Roger_Coville]

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

[Ashley_Validity]

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

[Roger_Coville]

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

[Hydey_Validity]

@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}))

[Hydey_Validity]

@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 ")

[Roger_Coville]

@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? 

[AnthonyValidity]

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!