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 

We have same version.

[knnwndlm]

@Emmanuel_G 

How do I modify the RegEx formula to exclude CALIFORNIA if it's part of the city such as CALIFORNIA CITY?  If I put that in, then all I'd get back is CITY.

Thanks,

K

[Emmanuel_G]

@knnwndlm 

One way of doing that using regex formula in attachement.

[knnwndlm]

@Emmanuel_G 

What I want is to have CALIFORNIA CITY stay as is since it's at the beginning and not at the end of the record.  Here's the workflow you showed me earlier:

How do I modify the RegEx or the Formula tool to pick up CALIFORNIA CITY and not drop CALIFORNIA since CALIFORNIA CITY is legit?

Thanks,

K

[Emmanuel_G]

@knnwndlm 

It's difficult to have one regex tool to match to this pattern because it will break the matching for many others patterns in other lines.

However, what I can suggest is to give this information mapping to Find and Replace tool and for the particular case of CALIFORNIA CITY, it will be handle and if you have just CALIFORNIA, it will be removed cause that it's state and not a city.

I have just done some adjustments in the workflow.

Is it ok right now ?