How to parse last string of numbers for certain rows where there are numbers

I have a column where in some rows there are two sets of 9 numbers separated by _. It's not always in the same place if I use text-to-columns. How can I extract those last two sets of numbers into two new columns and leave the rest blank? Sometimes it's a "0" and I want to leave that blank too.

I attached several examples and highlighted in yellow the ones I want it to apply to and the string in red that I want to extract.

Thank you!

Example.xlsx

Regex

Tips and Tricks

Parse

Preparation

Accepted answers

Claje

Hi,

Using the Regex tool in the Parse category, if you set the Output Method to "Parse", the following RegEx should do what you want (I have it set up so that it outputs the 0 when it appears, but you can apply replacement logic afterwards to handle this).

_(0|\d{9})_(\d{9})

I will edit this shortly to break down the RegEx and explain it.

EDIT: RegEx breakdown:

_(0|\d{9})

This is the first underscore, followed by either a 0 or 9 digits.

The parentheses create the first marked group that will be parsed out, so you can move these around to add the underscore if needed.

The "|" character signifies an "OR" function.

The \d{9} is 9 digit characters.

The second marked group is very similar to the first, but we have no 0's in the example so I did not include the 0| logic.  You could add this if it exists in your test case.

Second EDIT:
If you want to avoid using a formula to replace the 0's that appear later, the following RegEx looks to be a little better.

_(?:0|(\d{9}))_(\d{9})

The main difference here is that we have isolated the first marked group to \d{9}, and used an Unmarked group for the 0 OR check. This way, 0's will find a match, but will not appear in the final output, giving a blank value.

One thing to note is that line 8 which was not highlighted in your example also has matched output.

All comments

Claje

Hi,

_(0|\d{9})_(\d{9})

I will edit this shortly to break down the RegEx and explain it.

EDIT: RegEx breakdown:

_(0|\d{9})

This is the first underscore, followed by either a 0 or 9 digits.

The parentheses create the first marked group that will be parsed out, so you can move these around to add the underscore if needed.

The "|" character signifies an "OR" function.

The \d{9} is 9 digit characters.

The second marked group is very similar to the first, but we have no 0's in the example so I did not include the 0| logic.  You could add this if it exists in your test case.

Second EDIT:
If you want to avoid using a formula to replace the 0's that appear later, the following RegEx looks to be a little better.

_(?:0|(\d{9}))_(\d{9})

One thing to note is that line 8 which was not highlighted in your example also has matched output.

ivoller

Hi @Phyllis

Not sure exactly what you're trying to do but Regex is your friend. Maybe this will get you started

splitnums.yxmd

Phyllis

@Claje THANK YOU for the example and the thorough explanation!!! I hope I can apply these learnings to other needs in the future.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703