Take the last numbers after _ in a string

[Sammy22]

hello

i have the below type list and need to extract either the number which may be 1 or 2 digits long but is always after the last _, for example,

a_bread_2

b_tomato_12

c_apple_4

please help!

[danilang]

Hi @Sammy22 

Since you've got multiple _ in the string, using a regex tool Parse mode with .*_(.*) as the expression is your easiest option.  ".*_" matches everything up to the last "_".  (.*) captures everything after that.

Another option is to reverse the string, take everything before the "_" using a substring/findstring combination and then reverse the result.

Dan 

[Sammy22]

I am getting many nulls in my actual data using the regex for some reason. Can you show me how to use substring/findstring please?

[danilang]

Hi @Sammy22 

The regex string the I provided works for all the sample strings that you originally provided.  Chances are your actual inputs aren't as clean as your sample.  Can you post your current workflow along with the input files so we can look at what may be occurring?

For the non-regex solution, use a formula tool with multiple formulas in it. 

Formula 1.  Create a new field([Reversed]) with formula = ReverseString([Field]) 

Formula 2.  Create a new field([digits]) with formula =reversestring(substring([Reversed],0,findstring([Reversed],"_"))) 

Thanks

Dan

[MarqueeCrew · Accepted solution]

Formula Tool:

Regex_Replace([Field],".*_(\d+)",'$1')

 everything up to and including the last underscore is ignored

 the first group is a digit or multiple digits

 the output is just the group of digits. 

cheers,

 mark