community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Remove all letters in a string - Regex (?)

Hi masters

 

I have a field having the below strings and I would like to remove any letters ultimately leaving the numeric characters.

 

AGE67

2YR

18MTH

 

converted to:

 

67

2

18

 

I've tried the REGEX tool replacing all letters with Replacement Text of 'blank', it simply took away the whole string for some reason..

 

Help please,

Gundam

Magnetar
Magnetar

The following should work for your RegEx string, and then just use "$1" as your replacement value to keep only the digits in the marked group ()... there may be more glamourous ways to do it with RegEx, too, this is just one option!

 

[A-Z]*(\d*)[A-Z]*

 

Cheers,

NJ

Magnetar
Magnetar

Also, worth noting... there is a Data Cleansing tool that will do a similar thing, in the Preparation tool section. You can check a box to remove all letters in a string or strings - also has options to remove all punctuation, all numbers, duplicate whitespace, etc. Definitely worth checking out!

 

NJ

Highlighted

Ok I just found out that if I checked the output unmatched box at the bottom of the Regex tool it would work. I have also come up with an alternative using Regex_Replace formula with "\u". This allows me to add a new column with the Formula tool.

 

Thanks man, appreciate your help.

Gundam

Labels