topic Re: Search for INC but getting Vince really want " INC", but it goes away in DemandTools

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

Roger_Coville — Fri, 24 Mar 2023 13:58:44 GMT

@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

Re: Search for INC but getting Vince really want " INC", but it goes away

Hydey_Validity — Wed, 22 Mar 2023 21:02:42 GMT

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.

Re: Search for INC but getting Vince really want " INC", but it goes away

Roger_Coville — Thu, 23 Mar 2023 11:58:45 GMT

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

Re: Search for INC but getting Vince really want " INC", but it goes away

Ashley_Validity — Thu, 23 Mar 2023 12:40:00 GMT

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

Re: Search for INC but getting Vince really want " INC", but it goes away

Hydey_Validity — Thu, 23 Mar 2023 16:48:24 GMT

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

Re: Search for INC but getting Vince really want " INC", but it goes away

Hydey_Validity — Thu, 23 Mar 2023 19:35:46 GMT

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

Re: Search for INC but getting Vince really want " INC", but it goes away

Roger_Coville — Fri, 24 Mar 2023 12:56:25 GMT

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

Re: Search for INC but getting Vince really want " INC", but it goes away

Roger_Coville — Fri, 24 Mar 2023 12:58:28 GMT

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

Re: Search for INC but getting Vince really want " INC", but it goes away

AnthonyValidity — Fri, 24 Mar 2023 13:59:41 GMT

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!