Firm names parse

[aparna0208]

Hi,

I am trying to compare two columns which has firm names from two different data set. The issue is there are all these different strings at the end as INC, LLC, INC., CO etc. I tried to trim the last part of the string using trim and regex function but it doesn't seem to work. The input and expected output example is given below. Trying to see if there is any better and easy way to do this. Thank you in advance!

Input

ABC LLC

XYX INC.,

UIE Advisors, CO.

KJD, LLC.,

Output

ABC

XYX

UIE Advisors

KJD

[binu_acs · Accepted solution]

@aparna0208 use the regex formula

Regex_Replace([input], "[,\s]*(LLC|INC|CO)[\.,]*\s*$", "")

[jrlindem · Accepted solution]

@binu_acs  regex is a great example of how to do this by stripping off the pieces you don't need.  If you have other suffixes, though you will need to modify the function.

For an alternative that doesn't use Regex, give this a peak.  This assumes that you are always just removing off the last portion of the name, regardless of what it is.  The basis of this is Reversing the String, parsing, then reversing back.  Take a look:

Hope this helps, -Jay

[aparna0208]

@binu_acs This did the trick:) Thank you so much! :)

[aparna0208]

@jrlindem A new function and method I learnt today. This is awesome:) Thank you so much!

[flying008]

Hi, @aparna0208 

FYI.

Spoiler

REGEX_Replace(Trim([Input]), '[\s,]+\S*$', '')