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!
Solved! Go to Solution.
Maybe you can post the source data table and the expected result table, which will help everyone understand your needs.
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
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|$)", ''))
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