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

[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 😊

[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')

[binuacs · Accepted solution]

@Rive11115 

[Rive11115]

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]

Hi

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.

original data:

987_1

98765_2

865_7

876_17_1

874_1_3_5

865_O

876_O_10

876_O_1_2

765_P

new data:

987

98765

865

876

874

865_O

876_O

876_O

765_P