Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
The Expert Exam is now live online! Read about the specifics and what it took to bring it to life in the blog by our very own Elizabeth Bonnell!
SOLVED

How to parse a text string

Highlighted
5 - Atom

Hi, I have a column with text that isn't a consistent format. I've pasted an example of a few lines below. Some have a version number at the end, some have a version number in the middle, and some have no version number at all. Also, the version number isn't consistent. Sometimes it is v20-01, sometimes it's v 20-01. I need to parse only the version if there is one. Any tips?

 

Example data:

Checklist verification

Budget v20-01

Confirmation Template v 19-05

SAS v18-11a - John Smith

 

The result I want is:

null

v20-01

v 19-05

v18-11a

Highlighted
Alteryx
Alteryx

Here is a good starting point that may get you headed in the right direction. Workflow is attached. 

 

initial parsing.png

Highlighted
5 - Atom

Thanks Brandon! That helps. I was thinking I needed to put the v in quotes and was getting nowhere. It misses some of the versions though because they are v 19-05, not v19-05. Any thoughts on how to capture those?

Highlighted
Alteryx
Alteryx

Yep exactly, that's why I used the formula first that did Replace([Field1], " v ", " v")

 

This turns a space then v then space into just a space then v. This should make the adjustment so that it parses easier. 

Highlighted
9 - Comet

Hi @temoss 

 

If you don't care about the "v" at the front, you can use this regex command:

\d.*\d.

 

This will grab only the numbers and one trailing letter at the end of the group.

 

If this solves your issue please mark the answer as correct, if not let me know!

 

Thanks!

Phil

Highlighted
16 - Nebula
16 - Nebula

I would suggest a formula like:

REGEX_Replace([Field1], ".*?(\bv\s*(\d\S+))?.*?", "$2")

 

A little fiddly Regex in some ways but basically looks for v after a break followed by a number (possibly with white space after the v).

It then matches until it reaches a whitespace character (or the end of the string)

 

Seems to work on the samples you sent.

Highlighted
5 - Atom

Thanks for all the replies!

Labels