RegEx Formula Not Working

[knnwndlm]

Hi SME,

Below is an Excel file that shows the progression of correcting the ORIG_CITY to the various stages using the Formula and RegEx tools.  However, I'm still unable to figure out why the formula and regular expression not working properly.  What I want to do is to capture only the city name leaving out all the extraneous details attached to the end of the city name. 

Could you please help me figure out a way to do this in the most efficient manner perhaps consolidating all those steps?  Appreciate it.

Thanks,

K

[Emmanuel_G]

@knnwndlm 

Find in attachement a solution.

Is it what you want to do ?

[knnwndlm]

Hi @Emmanuel_G,

Thank you for your help.  Almost what I want.  Any state references such as CA, CALIF, CALIFORNIA, NV, or NEVADA should be removed as well.  In the above case, RIALTO CA remains in place as RIALTO CA.  It should be just RIALTO.

Thanks,

K

[knnwndlm]

@Emmanuel_G 

I just noticed that I also have some items with tick marks.  Do I the double quotes to enclose the tick mark in your formula? 

Thanks,

K

[Emmanuel_G]

@knnwndlm 

No problem ! Find in attachement an updated version of workflow which should handle these extra infos about states.

Let me know if it is ok or not please.

[knnwndlm]

Hi @Emmanuel_G,

I still have the characters scattered throughout.  And in the case of HUNTINGTON BCH.CALIFORNI, nothing's changed.  Could you please help me fix these issues?  Also, what does $1 mean?

Many thanks,

K

[Emmanuel_G]

@knnwndlm 

Are you sure to have downloaded the lastest version of workflow ?

In my case, I don't have any states or additionnal infos in the output anchor of cleansing tool. The regex tool is not the end of my data processing. Go to the cleansing one.

The $1 represents the first marked group. For example : I have a name Bill, Gates and I want to capture this name in two separated fields, I can use regex as (.+),(.+).

When I put the parenthesis. It means that I created a marked group. And to get it in output, I use dollars signs. So If I write $2 $1. I'll get Gates Bill because Gates is in the second group and Bill in the first one. Find more details in this article : https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-RegEx/ta-p/37689

[knnwndlm]

@Emmanuel_G 

Thank you for the info!

This is odd.  If I were to remove the last RegEx from the original workflow, I would get the following:

I believe that I have the latest Alteryx version:

Not sure why it only worked for certain records.  I already had a Trim function in place so not sure what else I could do.

Many thanks,

K

[Emmanuel_G]

@knnwndlm 

I don't have these issues in my workflow as you can see in the screenshoot below.

Did you download the more current version ? Find in in attachement if in doubt.

[knnwndlm]

Thanks @Emmanuel_G!  I'll check to see if there's another Alteryx.  Believe I have the latest.  Many thanks!