Function to parse phrase/word only regex

[pgensler]

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.

[MarqueeCrew]

^VA\s|\sVA\s|\sVA$

If used with case insensitivity will find it.

[SeanAdams]

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

[pgensler]

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]

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]

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 · Accepted solution]

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]

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]

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