Checking for multiple substrings and if not in list then change

Hello there,

I have multiple substrings that I would like check a field for, and if the substring isn't in the list, I would like to change it to another value.

Foe example, the field may contain the following 5 values

OWNER234
OWNER567
MAY234
MAY567
ANOTHER

I would like to check a single field for the substring values OWN and MAY, and if it is equal to those values, would like to change the value to OWN and MAY, and if the substring is not present, change to OTHER

So, based on each value in the sample above

Checking Value 1 = OWNER234
Change to OWN

Checking Value 2 = OWNER567
Change to OWN

Checking Value 3 = MAY234
Change to MAY

Checking value 4 = MAY567
Change to MAY

Checking Value 5 = ANOTHER
Change to OTHER

There several ways of doing it

You can use lookup replace function.
www.etl-tools.com/wiki/aetle/transformat...neous#lookup_replace

Or you can get first three characters and use "Is in list' validation function

I didn't know that "Is In List" works with a substring. Do I need to use anything to indicate that it is a substring (for example %)?

We also have sub string function