Parse any digits after the last underscore

Question

Hi,

Need some help to parse any digits after the last underscore from the input. thanks in advance.

InputOutputPLATE_Nova H2_18PLATE_Nova H2PLATE_Monkey Bay DB2_1PLATE_Monkey Bay DB2PLATE_MIN_Naaran_Camden_A5_11PLATE_MIN_Naaran_Camden_A5PLATE_MIN_Daisy chani DF3_Rely A_5PLATE_MIN_Daisy chani DF3_Rely A

Gawa · Accepted Answer

hi @jenniferte Regex would help. For example, you can use Regex_Replace function in Formula tool.

REGEX_Replace([Input], "^(.+)_\d+$", "$1")

Gawa · Accepted Answer

As @Qiu said, Text to Column tool is a good option to parse data with a particular delimiters.

Please note that Text to Column searches delimiters from left to right, so in this case, you would get the data like below, because the first _ comes after the word "PLATE":

If you are not familiar with Regex and prefer Text to Column tool, use ReverseString function before parse it, and revert to original order by one more ReverseString function.

flying008 · Accepted Answer

Hi, @jenniferte

Another way by formula:

Left([Input], Length([Input]) - FindString(ReverseString([Input]), '_') - 1)