How to remove part of a string that comes after last special character

[Patricia2]

Hi,

I'm looking to remove everything that comes after a special character in a string. The thing is that is not a specific number of letters after that character (it can vary) and also the special character appears many times before. For example from a string like this: "coffee_milk_sugar" I'd only want to remove "_sugar" and keep the rest. Is there a tool that I could use to only remove whatever comes after the LAST underscore??

Thank you!!

[AbhilashR]

Regex tool would be able to help you achieve this ask, or you could write a formula in the formula tool if that helps.

Attached is a sample implementation of the Regex tool.

[Patricia2]

Thank you, but according to that RegEx it would find any mention of the underscore and remove that. I what to be sure it only removes what comes after the last underscore in the string. Is that possible? Thanks

[AbhilashR]

It depends on how you setup your RegEx string, The sample solution I shared will always look for the last underscore and give you the string after it. Below is a sample screenshot.

[Patricia2]

Ok i see, i think it might work then. However what I need is also to get it removed from the original string. Would a REGEX_Replace with a similar command work to do that?

Thanks

[AbhilashR]

I have reattached an updated solution that modifies the existing field using regex_replace using both the Regex, and Formula tool.

[fmvizcaino · Accepted solution]

Hi @Patricia2 ,

Only another possibility, you could use the following function in your formula tool.

REGEX_Replace([Field1], '(.*)_([^_]+)', '$1')

Best,

Fernando Vizcaino

[Patricia2]

It worked, thank you