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!!
Solved! Go to Solution.
Hi @yaser
This should work
Use a Formula Tool
Trim(REGEX_Replace([Field], "(\D+)\b\d*.*", "$1"))
Cheers,
Regular Expression to the rescue! I prefer use in a formula tool
regex_replace([Field1],'^([\D]*)\d.*','$1')
beat me, dang it.
NICEEEEE!!! Thanks ALotttt!
Could you please explain how you created it and the logic behind it? I got so confused :'(
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
Cheers,
That's great!! Thanks a lot! looking at the website now!