Matching words before and after a comma

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!

Error Message

Preparation

Accepted answers

flying008

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|$)", ''))

录制_2022_07_30_09_57_24_554.gif

All comments

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

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|$)", ''))

录制_2022_07_30_09_57_24_554.gif

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

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335