RegEx

[knnwndlm]

Hi RegEx Pro,

I'm trying to build a RegEx to tease out the name of the city without the extraneous info at the end.  For example, here's my table:

HESPERIA CA

HESPERIA, CA

HESPERIA. CA

HESPERIA,, CA,

BOULDER& NV

BOULDER&NV

What I want to capture is HESPERIA and BOULDER leaving out the characters.  The way I can tell that it's the end of the city name is that we have char(s) and/or space(s) before CA or NV.  I've been doing this the long way by using RegEx and Text To Columns with one character at a time.  I'm trying to figure out a way to factor in other miscellaneous characters before CA or NV that I might not be aware of.

Could somebody help me figuring out an expression in RegEx that will encapsulates all the miscellaneous characters before CA or NV and leaves the city name untouched?

Thanks,

K

[DataNath]

Could perhaps use something like this before consolidating the 2 fields together with a formula tool or something:

(.+\w).+\b\w{2}\b|(.+)

[knnwndlm]

Thanks @DataNath!  That works, I can consolidate the two fields into one.  

[knnwndlm]

Hi @DataNath,

I noticed a couple more issues when I added more cities.  Some of the city name letters got chopped off while others only picked up the first word of the city name.  Could you please help me figure out how to revise the RegEx to accommodate for those exceptions?

Thanks,

K

[DataNath · Accepted solution]

@knnwndlm I think the best approach here really is a quick cleanse step beforehand, then we can just use the same RegEx with a slight amendment which gets the desired output. Just be sure to bump up the size of the RegEx output fields a little to allow for bigger strings so that they're not truncated:

[knnwndlm]

Thank you @DataNath/!  Much appreciate it.

[knnwndlm]

Hi @DataNath ,

How do I incorporate cases in which the entire state name is spelled out in the city field?  Here are a few examples:

HESPERIA, CALIFORNIA

HESPERIA CALIFORNIA

BOULDER CITY, NEVADA

I suppose that I could do a separate RegEx as follows:  \s*CALIFORNIA\s*|\s*NEVADA\s*.  Do I need a grouping for alternate?  Or can this be left as is.

Thanks,

Konn

[knnwndlm]

Hi @DataNath, @ConnorK 

How do I encapsulate all the cases in RegEx as shown in the Excel file below?  I was hoping to put all the various punctuations in {  } in the RegEx as a way to segue out the extraneous info.  However, I have no idea how to do that.  What I want to have at the end of the day is to correct the ORIG_NAME to the CORRECTED_NAME in the Excel spreadsheet.

Could you please help me understand how to do this?  I'm learning RegEx and it's quite challenging especially for those complicated ones.

Thanks,

Konn

[ebledsoe22]

Hello. I am new to using this particular tool. I am trying to parse out the phrases below so that they only read "Foreign" or "Domestic". Can someone help me build this expression? I am still learning what each component means.