Formula to Find "Bob And Mary Smith" and "Steve and Lisa Jones"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-22-2023 02:00 PM - last edited on 03-24-2023 06:58 AM by AnthonyValidity
@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-22-2023 02:02 PM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-23-2023 04:58 AM
@Hydey_Validity Where can I find the documentation on % or _ ? No luck searching the doc for these.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-23-2023 05:40 AM
@Roger_Coville Here is a document that reviews % and _ in record selection criteria.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-24-2023 05:55 AM - edited 03-24-2023 05:56 AM
Thank you @Ashley_Validity - This needs clearer "Billing" in the documentation!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-23-2023 09:30 AM - edited 03-23-2023 09:48 AM
@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}))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-23-2023 12:35 PM
@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 ")
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-24-2023 05:58 AM
@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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-24-2023 06:59 AM
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!
Senior Customer Success Manager