Trim strings from certain words

Hello,

I'd like to trim string records beginning from certain words. For instance I have companies' names such as XYZ Limited, XYZ LTD, XYZ AG, XYZ GmbH, XYZ

Gesellschaft mit beschränkter Haftung.

Is there a way to trim these records beginning from predefined words, for instance "LTD", "Gesellschaft". I was thinking of RegEx toll but I can't figure out a way how to apply it to this issue.

Does anyone have an ideas how to overcome this problem?

Transformation

Preparation

Accepted answers

NicoleJohnson

Hi! If you'd like to combine solutions to make it more dynamic, you could do something like the following:

1. Create a text input list of keywords as @tom_montpool suggested

2. Use a Formula to add a RegEx space character \s at the beginning and .* at the end of each keyword to capture the leading space and any characters that might be after the keywords

3. Summarize Tool to concatenate the keywords together, separated by a | to indicate "or"

4. Append concatenated string to the list of company names

5. Use RegExReplace formula in the Formula tool to replace anything that matches the keywords in your concatenated list with "" (the formula version of @Joe_Mako RegEx Tool solution):

REGEX_Replace([CompanyName],[Concat_RegExString], "")

This way, if you found more company suffixes you wanted to add to the list (Inc., Co., etc.), you'd only need to add them to the bottom of the text input list and run again, rather than having to go in and insert them into the RegEx Tool formula. (Note that it still needs to be case sensitive the way I've shown it above.)

Cheers! NJ

RegEx Example.yxmd

All comments

Bob_Blackey

You can use the Regex Tool with the Replace functionality.

For example to Parse starting with XYZ

(XYZ)(.*)

and replace it with the first marked group $1

that would pull out just the XYZ

If you change the order you could get the text before or after your keyword.

I think if you play around with that option you could get want you want. If you could supply a couple of sample records with the before and after that would be great.

Cheers,
Bob

tom_montpool

I'd suggest that you put the strings you want to remove (e.g. LTD, gmbh) in a Text Input tool in a "Find" field and leave blank/empty values in a "Replace" field and then use the Find Replace tool.

You could also nest Replace() functions in the Formula tool:

REPLACE(REPLACE([CompanyName],'LTD',''),'gmbh','')

...but remember that the Formula approach is case-sensitive.

Joe_Mako

You can use the Regex:

set to case insensitive, and a blank replace.

the "\s" is a space character, "$" means end of string, and "|" means OR. All together this means if it find any of these phrases at the end of a sting, it will remove them.

remove suffix.png