About EasyMorph Tutorials & Examples Web-help

How to look up and replace a string of text

Hi

I hope this isn’t a dumb question as I’m new around here. I’ve managed to get a long way through my first project but hit a block I can’t solve so I’m hoping somebody with more experience can advise.

I have 2 tables.
Table 1 is my primary table,
Table 2 is for lookup data.

I need to look in a column in Table 1 to see if any of the entries from a particular column in Table 2 are contained in the text string. If found, replace with a value from another column in Table 2, if not, do nothing.

So for example

Table 1 - string in “High tide time in Laguna Beach”

Table 2 - lookup column “High Tide”
Replace with “<>”

updated Table 1 string would read “<> time in Laguna Beach”

Can anyone advise a method to achieve this please? Thanks in advance.

Hi Rob,

Welcome to the Community! There are no dumb questions here, especially from newcomers :slight_smile:

See the example below. Let me know if you have questions about the logic.

Note that in your example the lookup value “High Tide” has the 2nd word (Tide) capitalized while in the sample text string it’s not. I’m not sure if it was on purpose or not. In the example I assume that matching and replacement should be case-sensitive.

substring-lookup-replace.morph (3.3 KB)

***** The topic title changed to a more descriptive one

Thanks for the fast feedback, really impressed, I’ll test it now.

You’re welcome! :slight_smile:

I’ve implemented this and it works really well with one exception. It now matches partial words. Yay

This has though created another issue in that the solution matches partial words. For example, from the case shared, if another lookup was “each” this would be matched with the word beach.

Is there a way to prevent this or alternatively cleanup after the fact, as I need to only match whole words.

Thanks

It’s not possible out of the box. The solution would be to use all possible combinations of whitespace and punctuation, such as:

" beach "
" beach."
" beach,"

etc.

Alternatively, you can split sentences into words using the “Split delimited text into rows”, then match entire words. But that would make it hard to detect key phrases that consist of more than 1 word.

Finally, one can use the “Regular expression” action together with iterations, but that’s a very technically non-trivial approach.

I found a solve on this - worth a share:

in my main table I added a " " (space) to the start of the string and the end. So every word in the string started and ended with a space.
I then did the same in the lookup table

Did the match, and then removed any redundant spaces. Works great.

Thanks for the steer.

1 Like
To learn more about EasyMorph visit easymorph.com.