Take the last numbers after _ in a string
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
Solved! Go to Solution.
- Labels:
- Developer
- Developer Tools
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
