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

Hi @dgudkov,

Hope you are well.

I am working on something similar whereby I would like to do multiple find and replaces within a string of text. See the screenshot below:

As you can see, only the first instance is replaced. What is the best way to handle this situation? I was thinking of adding lots of replace steps but this will be difficult for people to understand in the long term if the number of replacements grows. I have seen this kind of feature in other software whereby they have a word or synonym library.

I look forward to hearing from you.

Regards,

Shaheed

Hi Shaheed,

in this case the only option would to repeat replacements multiple times.

It seems like we need a special action for synonym replacement. We had similar requests from other users, I’ll see if we can add it to our roadmap.

Thanks for the response @dgudkov. I think you are probably right. I just tried iterating through the column values, exporting each individually and performing various replace actions based on a table and it as taking much longer to process than a series of replacement actions.

If you do some sort of synonym replacement, perhaps it might be an idea to have a fusion between these two current features:

image

To learn more about EasyMorph visit easymorph.com.