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 IDString Search SubstringMatch Flag1BUTTERBUTNo Match2TIS BUT A SCRATCHBUTMatch3HALIBUTBUTNo Match4REBUTTALBUTNo Match5BUTBUTMatch6BUT WHYBUTMatch7ENDING SENTENCE WITH BUTBUTMatch 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!

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

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!

Parse

Accepted answers

fmvizcaino

Hi @froseph ,

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

Let me know if you need further assistance.

Best,

Fernando Vizcaino

2020-05-11 Formula to search for substring inside string where not part of another word.yxmd

jdunkerley79

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.

Regex Words.yxmd

All comments

fmvizcaino

Hi @froseph ,

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

Let me know if you need further assistance.

Best,

Fernando Vizcaino

2020-05-11 Formula to search for substring inside string where not part of another word.yxmd

jdunkerley79

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.

Regex Words.yxmd

grazitti_sapna

Hi @froseph ,

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

Solution.yxmd

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!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703