Using Regex_Replace

[nick22]

Hi, 

I'm new to using the Regex functions in general. But I am using a Regex_Replace inside of the formula tool to search a string field for a sub-string and then return just that.

So for my formula reads like this

if ...

then left(REGEX_Replace([Item Description], "[^V.\d$]", ''),4)

else...

For many of my cases this formula is working great but i have many different products and in some cases the product title is causing problems.

For example:

First if there is a V anywhere in the products name it pulls that V in front and then grabs the Version. I'm trying to pull just the version out of the item description which is buried, and depending on the product the version is in different places in the string.

Second the \d is grabbing all of the digits in the string after the regex finds the "V". How can I limit that to just two digits? So far the Left function is limiting it to just four characters but when the item has a "V" in the name and the version the minor version is dropping off.

for example:

EMPLOYEE V8 - Formula Works

LEAVE V8  - Generates VV8 (need V8)

ISERIES V6 PAYROLL,2D - Generates V62 (need V6)

Any Suggestions on how to tweak the Regex_Replace to read these fields and account for these scenarios, is greatly appreciated.

[Thableaus]

Hi @nick22 

EDIT: just saw you need at most 2 digits

REGEX_Replace([Field1], ".*(V\d{1,2}).*", "$1")

\d{1,2} finds 1 or 2 digits.

Cheers,

[jdunkerley79]

I suggest:

REGEX_Replace([Item Description], "^.* (V\d+).*$", "$1")

To explain the Regex_Replace:

- The '^.* ' matches from the beginning of the string until a space

- The next part '(V\d+)' matches the V and following numbers and labels it into $1

- The last part '.*$' matches until the end of the string

Sample attached

[nick22]

@jdunkerley79  @Thableaus 

Hi Guys,

Thank youfor getting back to so quickly both of these formulas gets me very close. Although wit the REGEX formatted like this if there is a minor version for example V8.1 the functions cuts of at the period. I need the one digit after the period if it is there. It won't be there in all cases though.

[nick22]

@jdunkerley79  @Thableaus 

Sorry about that forgot to add that scenario to my original post.

[jdunkerley79 · Accepted solution]

Try:

REGEX_Replace([Item Description], "^.* (V\d+(\.\d+)?).*", "$1")

This will allow for optional minor revisions 

[nick22]

@jdunkerley79  Thank you!! 

[Thableaus]

I'd leave a similar solution as @jdunkerley79 did.

REGEX_Replace([Field1], ".*(V\d{1,2}(?:\.\d{1,2})?).*", "$1")

Cheers,