Parse everything before number?

[yaser]

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!!

[Thableaus · Accepted solution]

Hi @yaser 

This should work

Use a Formula Tool

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

Cheers,

[ephij · Accepted solution]

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

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

[ephij]

beat me, dang it.

[yaser]

NICEEEEE!!! Thanks ALotttt! 

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

[Thableaus]

@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]

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