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

[Roger_Coville]

I'm building out a modify to my Accounts object.  We have record types for People (Households) and Businesses/Churches (Organizations) - I'm trying to change some businesses with INC in their name but Vince also contains those 3 letters.  I really want " INC" - with a blank space before the inc letters.  When I add the quotes in my record selection criteria I use the quotes but they go away.  How can I achieve my space/INC query?

Thanks,

Roger C. 

[JonG_Validity]

Hi @Roger_Coville, I ran some testing on this and found a solution that might work better for you. Rather than filtering within the Record Selection Criteria for "INC", it might be best to utilize a RegExReplace formula in the Set New Values section (step 2). You can still set a Record Selection Criteria stating "Name contains INC" if you'd like to narrow down your search.

By using the RegExReplace formula in Modify, you can find a specific string value and replace it with a value of your choosing - you can copy the formula below and test it out within your DemandTools instance to see if this works for you:

RegExReplace(${Name}, "(?i)\s+inc$", " Inc.")

What this formula is doing is finding any variation of " inc" (case insensitive) with a preceding space, and replacing it with " Inc.". I have tested this within my org and it ignores any situation where there is already a following period, and also ignores account names that include values such as Vince and Incorporated; of course, we always recommend you test this internally before processing any records.

I would also love to direct you to our Modify Formula Dictionary! I keep this on hand at all times in case there's something I am trying to do accomplish but can't quite figure out on my own.

I hope this helps!

[Roger_Coville]

@JonG_Validity Thanks for the insight on my challenge.  I'm still learning the tool, but wouldn't every record selected run through the second step?  Meaning, I may be able to change just my desired records name to be something like was " Inc."  to. " INC." - my goal is to change each record from a Household to an Organization record type.  This would include my Vinces' changing too?  The long term goal is to automate ths process to not require human review.  I could change all the " inc" to " zzzyyy" and then take a second pass at the data looking for " zzzyyy".  This would skip all my Vinces'. 

[Hydey_Validity · Accepted solution]

Roger,

you should be able to use % INC% which will match to anything with INC in it but has a space before the INC.

Chris

[Roger_Coville]

@Hydey_Validity Thanks for this idea - I will pursue this option today!!

[Hydey_Validity]

Just to clarify further @Roger_Coville , you'll need to use the 'Like' operator, not 'Contains'.  

[Roger_Coville]

@Hydey_Validity 

Chris - the Like and Contains both produced the same results.  Great solve!  Thanks again.

[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.