Parse any digits after the last underscore

[jenniferte]

Hi,

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

Input	Output
PLATE_Nova H2_18	PLATE_Nova H2
PLATE_Monkey Bay DB2_1	PLATE_Monkey Bay DB2
PLATE_MIN_Naaran_Camden_A5_11	PLATE_MIN_Naaran_Camden_A5
PLATE_MIN_Daisy chani DF3_Rely A_5	PLATE_MIN_Daisy chani DF3_Rely A

[Qiu]

@jenniferte 
You can do it with a Text to Column tool and set the delimiter to underscore. 😁

[gawa · Accepted solution]

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

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

 

[jenniferte]

The solution works well. Thank you!

[gawa · Accepted solution]

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.

[Qiu]

@gawa 
Thanks for the comment. You are correct.

[flying008 · Accepted solution]

Hi, @jenniferte 

Another way by formula:

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