Matching words before and after a comma

[christianCaldwell]

Hi There, Regex noob here,

I need to replace the city and state at the end of strings like these (out of over 54k records,) 

COMPANYNAME, INC. FRANKLIN, TN
ANOTHERCOMPANYNAME, ROCHESTER ROCHESTER, NY
COMPANYNAMEDIFFERENT, INC. HOUSTON, TX
PARTNER, PARTNER, PRINCIPAL & ANOTHER LAWYER, PLLC TROY, MI

I'm doing it with the following formula

TRIM(REGEX_Replace([CompanyName_new], "(\S+)\s*\,\s*(\S+)+$",""))

But some of the names on the list are causing it to "exceed predefined bounds" and recommends "refactoring the regex to make choice unambiguous.

I've looked into the error and am not sure what part of this is causing a potential loop. Any help is greatly appreciated.

Cheers!

[flying008]

Hi,@christianCaldwell 

Maybe you can post the source data table and the expected result table, which will help everyone understand your needs.

[christianCaldwell]

thanks @flying008  The source data is far too sensitive. I hope this clarifies what I'm trying to do

Original

COMPANYNAME, INC. FRANKLIN, TN
ANOTHERCOMPANYNAME, ROCHESTER ROCHESTER, NY
COMPANYNAMEDIFFERENT, INC. HOUSTON, TXPARTNER, PARTNER, PRINCIPAL & ANOTHER LAWYER, PLLC TROY, MI

Expected Results

COMPANYNAME, INC.
ANOTHERCOMPANYNAME, ROCHESTER
COMPANYNAMEDIFFERENT, INC.
PARTNER, PARTNER, PRINCIPAL & ANOTHER LAWYER, PLLC

[flying008 · Accepted solution]

Hi, @christianCaldwell 

I'm not sure how you process the part "COMPANYNAMEDIFFERENT, INC. HOUSTON, TXPARTNER" to "COMPANYNAMEDIFFERENT, INC. PARTNER", so maybe you can explain it.

the main RegEx expression: 

IIF(Contains([From], "TX"), REGEX_Replace(REGEX_Replace([From], "\s\w+\,\s\w+(?=\n|$)", ''), "(?<=\.)\s\w+\,\s\w{2}", "\n"),REGEX_Replace([From], "\s\w+\,\s\w+(?=\n|$)", ''))

[christianCaldwell]

Hey, This is great @flying008. Thank you very much. That TXPartner was a failure to add the line break back in when I replied to you friday. there's never anything after the state abbrev.

The approach is really smart and will work for what I'm doing. Thanks again