Formula to search for substring inside string where not part of another word

[froseph]

Hi All,

I'm trying to use a formula to perform a "match", by searching for a substring inside a string field, where the substring is not part of another word (i.e. preceding, ending or being contained within another word comprised of letters).

For example, I want to search for the substring 'but' across a dataset, and produce the output below:

Column ID	String	Search Substring	Match Flag
1	BUTTER	BUT	No Match
2	TIS BUT A SCRATCH	BUT	Match
3	HALIBUT	BUT	No Match
4	REBUTTAL	BUT	No Match
5	BUT	BUT	Match
6	BUT WHY	BUT	Match
7	ENDING SENTENCE WITH BUT	BUT	Match

I'm currently using a Contains function, however this will provide a match for any of the above strings, which is not what I'm looking for. My next step is to experiment with FindString and Contains, but I would imagine there's a more elegant solution (possibly using RegEx?)

Would appreciate any support on this requirement, or to be pointed in the direction of any other similar asks (which I could not find on Community)

Thanks!

[fmvizcaino · Accepted solution]

Hi @froseph ,

Attached is a solution using a find/replace + formula tool.

Let me know if you need further assistance.

Best,

Fernando Vizcaino

[jdunkerley79 · Accepted solution]

Hi,

If you want to use RegEx the trick is the \b to identify a word boundary. Converting this into a formula becomes:

REGEX_Match([String ], ".*\b" + [Search Substring] + "\b.*")

Have attached a sample to get you started.

[grazitti_sapna]

Hi @froseph ,

Please take a look for the attached solution using RegEx tool.

[froseph]

Perfect - Thanks Fernando, this works great. In the end I opted for a RegEx solution as the match was embedded in a slightly larger formula. 

[froseph]

Many thanks - This is exactly what I was looking for and works a charm!