How to parse a text string

[temoss]

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

[BrandonB · Accepted solution]

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

 

[temoss]

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]

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]

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

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]

Thanks for all the replies!