Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Function to parse phrase/word only regex

pgensler
8 - Asteroid

Is there any way to parse out  a whole word/phrase only using one of the string functions? For Example, I have text that looks like this

 

Falls Church VA

Spring Valley Medicine

 

Now, I would like to match for only VA, not for Valley. I can easily accomplish this using the Find Replace tool by using  Match whole word only.

Is it possible to match only VA using regular expressions, or even to utilize a string function such as GetWord to match that exact word/phrase? Is this even possible with the functions provided in Alteryx? Thanks for your help.

8 REPLIES 8
MarqueeCrew
20 - Arcturus
20 - Arcturus
^VA\s|\sVA\s|\sVA$

If used with case insensitivity will find it.

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
SeanAdams
17 - Castor
17 - Castor

Nice one Mark,

@pgensler - here's a slighly different Regex approach:

 

 

  • http://regex101.com is my best friend - you can easily play here, and learn RegEx the easy way
  • (?i)\b(VA)\b is a good regex to only pull whole word VA
    • breaking this down:
    • (?i) is a command to Regex to be case insensitive
    • using parse version of RegEx - whatever is in brackets is kept for you in the output.   
    • \b is a word boundary
    • So in this case - what this says is "keep 'VA' for me if it's between any 2 word boundaries"

This should get you to an answer, and also help your regex exploration :-) 

Have a good weekend Peter

Sean

 

2017-05-27_11-50-28.png

 

pgensler
8 - Asteroid

Could you use the Getword function instead to find the word/phrase VA? I'm just surprised this functionality is not really part of the built in string manipulation functions.

SeanAdams
17 - Castor
17 - Castor

Hey Peter,

The Getword function is made to get you word number X.  So, for example, if you want word 3, use Getword("this is my string",3) which will return "my".

 

Did the regex work for you and get you to a solution?   Hopefully we can close this out (mark as solved) and get you back on the road again.

 

Cheers - have a good Monday Peter

Sean

pgensler
8 - Asteroid
The regex works great, my concern is that this is pretty simple functionality that should be baked into string functions, which it is not. Regex is useful for situations like this, but I'd rather not have to use if it's not absolutely necessary. Thanks for your help.

Get Outlook for iOS
SeanAdams
17 - Castor
17 - Castor

Very glad that this worked.

There's a few threads about functions that people would find helpful under Ideas.    Not sure if you are aware but @jdunkerley79 has built a set of add-in functions that you can download, and I've got a request in the ideas queue to include many of these in the core product.

 

If you want to post this in the ideas section, I'll follow up and vote for it (I agree that this would be useful).

 

For this thread - would you mind marking it as solved to close it out (just hit the solution button under one of the posts) - that way it can add to the knowledge base, and come up in user searches from within Alteryx.

 

Thanks Peter - I'll have a look out for your item in Ideas and vote for it when it comes up.

 

Cheers

Sean

 

pgensler
8 - Asteroid

Sean,

 

Thanks, I have posted this in the ideas:

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Add-parameter-to-get-word-function/idi-p/6228...

 

Do you know where I can download the functions from  @jdunkerley79 ??Are these available on the gallery, or how I can access them?

SeanAdams
17 - Castor
17 - Castor

The latest version of @jdunkerley79's formula addins are here:

https://jdunkerley.co.uk/2016/05/15/alteryx-formula-add-ins-v1-1/

 

and his blog is absolutely excellent - exposes capabilities that most folk are not aware of and makes them very approachable.

 

Happy hunting Peter

Sean

Labels