We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Parse everything before number?

yaser
7 - Meteor

Hello Life savers!!

 

I have a question. I have a  column that has numbers and words and spaces. I want everything before numbers.

 

Following is how it looks like:

 

Wingate at Weston 75 Norumbega Road
E2 Franklin Square
The Blackstone 33 Blossom St
Woodsmallen shelter 794 Massachusetts Ave
Meacham St 4A
C Julius Rubin Court 6
RFD 1 BX 83 PODUNK RD

 

so I want everything before the number. for example "Wingate at Weston" form the first and so on.

 

Please help!!

 

 

6 REPLIES 6
Thableaus
17 - Castor
17 - Castor

Hi @yaser 

 

This should work

 

Use a Formula Tool

 

Trim(REGEX_Replace([Field], "(\D+)\b\d*.*", "$1"))

 

Cheers,

ephij
9 - Comet

Regular Expression to the rescue!  I prefer use in a formula tool

regex_replace([Field1],'^([\D]*)\d.*','$1')

 

ephij
9 - Comet

beat me, dang it.

yaser
7 - Meteor

NICEEEEE!!! Thanks ALotttt! 

 

Could you please explain how you created it and the logic behind it? I got so confused :'(

Thableaus
17 - Castor
17 - Castor

@yaser 

 

no problem!

 

So basically the expression captures non-digits - 1 or more times - (\D+) that come before a word delimiter (\b, which in this case, most of times, it's a space), a digit 0 or more times (\d*) - this is so the expression won't fail if there's no digit before a word delimiter, and then everything else after that is put as (.*), which it means any character 0 or more times.

 

REGEX_Replace([Field], "(\D+)\b\d*.*", "$1"))

 

You reference the captured expression in parenthesis with the "$1", which is basically what you want as an output.

 

If you wanna know more about REGEX, I recommend you taking a look at this website

https://www.rexegg.com/

 

Cheers,

 

 

yaser
7 - Meteor

That's great!! Thanks a lot! looking at the website now!

Labels