Extracting only Numbers from a cell ( which also contains letters &Underscores)

[Masond3]

Hi All ,

Hoping this is an easy one,

How do i extract only the numbers from "Current" Column 

Current	ExpectedOutcome
L1_AUS_123123	123123
AUS_123123	123123
L5_AUS_123123	123123
L1_NZ_111111	111111
AUS_111111	111111

Looking forward to your assistance 

Regards

Masond3

[hellyars · Accepted solution]

@Masond3   Try. ^.*\_(\d+).   This looks for the second underscore then grabs all numbers after it. 

 

[hellyars]

@Masond3   Here is a simple workflow. 

[sguthrie · Accepted solution]

You can use the data cleaning tool to remove punctuation and letters.

[MarqueeCrew]

@Masond3 

REGEX_Replace([Current], ".*_(\d+)", '$1')

That's for a formula tool....

You can also do this magic:

Right([Current],findString(ReverseString([Current]),"_"))

Cheers,

Mark

[Masond3]

You can indeed  - Sometimes the obvious is staring you right in the face. 

Thank you 🙂 

[hellyars]

@Masond3  This approach also captures any # at the start of the sequence.   That's fine if you want to capture any at the start, but your example suggested you only want to capture ending sequence of numbers. 

[apathetichell]

Just a quick question - is the number always going to follow an underscore? Also - will the series of numbers always be two or longer? @hellyars solution is great if the first question holds true. If not you'll have to post more data samples or explain more of the structure of the data which you are trying to capture.

If for some reason there first part doesn't hold true - but the number code you are trying to capture is always of a certain length you can use a slightly different regex parse code like:

(\d{2,}

or even (\d{6}) - if you area always looking for exactly 6 digits in a row.

[Masond3]

Correct - The number will always follow the Underscore and the length of the number can vary

[apathetichell]

@hellyarsapproach will work - Datacleanse cannot differentiate between the 1 in "L1" in L1_AUS_123123 and the 123123 code. so it will give you 1123123 not 123123.