Numeric wildcard

Rive11115

I’m a beginner in alteryx and I’m hoping someone can help:

I need to extract characters before an underscore followed by a one digit number.

Original Data:

89H876_1

89FT865_2

7655_1

6645GT_R

New Data should look like this:

89H876

89FT865

7655

6645GT_R

I tried using this formula but i’m limited by hard coding the digit.

if contains([Original Data],”_1”)

then left ([Original Data],findstring([Original Data],”_1”))

else [Original Data]
endif

Common Use Cases

Accepted answers

binu_acs

@Rive11115

Regex_220124.yxmd

All comments

alexnajm

How about this?

if contains([Original Data],”_1”) OR contains([Original Data],”_2”)

then Regex_Replace([Original Data],"(.*)_\d","$1")

else [Original Data]
endif

It can be made more robust for other scenarios, but this options works for the current data 😊

Numeric wildcard.yxmd

cjaneczko

Try the following in a Formula tool

REGEX_Replace([Field1], '^([a-zA-Z0-9]+[a-zA-Z]+[0-9]*|[a-zA-Z0-9]+)_?[0-9]*$', '$1')

Hi,

how can I modified this line:

contains([Original Data],”_1”) OR contains([Original Data],”_2”)

so it would work for any number between 0-1 instead of 1 OR 2 only.

Rive11115

Thank u so much your formula works!

im discovering new data where some of the data I want to scrub will have an additional _ or the first number after _ could be two digit ie _17. Can you modify your formula to remove the underscore and everything after if a number is after the first underscore only.