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!
Solved! Go to Solution.
Hi @froseph ,
Attached is a solution using a find/replace + formula tool.
Let me know if you need further assistance.
Best,
Fernando Vizcaino
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.
Many thanks - This is exactly what I was looking for and works a charm!