String Deletion By Searching for specific values at the end of the string

[AbhishekkSinghh]

Hi, 

I am fairly new to Alteryx and cannot think of a way to solve this problem.

I have a column with string values which end with words such as US or LST and I want to blank those rows.

I don't think I can use regex because the strings with last words such as US or LST don't have a fixed pattern.

Here is an example of what the data looks like:

SHORTNAME <- Column Header
A5C
CBUS
DCN
NCHUS
A3BLST
12US

I tried using formula tool but it didn't work. Please see the attachment.

[ShankerV · Accepted solution]

Hi @AbhishekkSinghh 

Please use the below Regex tool.

Column to Parse: 

SHORTNAME

Regular Expression:

(.+US)|(.+LST)

Output method:

Replace

Replacement Text:

Keep it empty/blank, as the requirement is to blank out that cell

Many thanks

Shanker V

[DataNath · Accepted solution]

Hey @AbhishekkSinghh, if you have a larger list of these values then I'd recommend building a lookup table. From there, you can use a Find Replace tool to append this lookup value wherever it's found anywhere within your list of words. After that, we can write a simple expression to say when the word ends in the lookup value, null the cell, otherwise leave it. Have attached an example workflow for you to look over.

[AbhishekkSinghh]

Hi Shanker, Thanks! that works. 

Could you tell me for further reference what (.+) meant, I googled it and from what I understood, it seems like (.)dot is used to say look at the end of the sentence and (+) means one or more. Is that right?

[AbhishekkSinghh]

Hi @DataNath your solution works! I found it very interesting the way you used find and replace tool. Thanks for providing the example through a workflow. 

[ShankerV · Accepted solution]

Hi @AbhishekkSinghh 

. refers to any character (including letters, digits and special characters)

+ refers to more than one count which is more dynamic

Hope this helps!!!!

Many thanks

Shanker V