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

How to parse a text string

temoss
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

6 REPLIES 6
BrandonB
Alteryx
Alteryx

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

 

initial parsing.png

temoss
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?

BrandonB
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. 

Maskell_Rascal
13 - Pulsar

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

jdunkerley79
ACE Emeritus
ACE Emeritus

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.

temoss
5 - Atom

Thanks for all the replies!

Labels