Calulated Result and Text Parsing

Greetings All,

My KB has a field with a unique string of text (ex. 12345-18-XX). The data / text that is inputted into this field is generated outside of Agiloft and (for reasons I won’t go into here) can’t be changed.

It would be a big help if someone could tell me if its possible / how to parse text from the above field and place a portion of the string into a new field. For instance, if 12345-18-XX is inputted, I’d like another field to automatically know that the “-18-” means 2018, “-17” means 2017, etc.

Basically, I’m looking to do something similar to the “text to column” function in excel. I believe this can be done using the calculated result field, but am not a coder by any means, and appreciate any guidance.

Hi Jacob,

This is possible with the replace() function and Regex. There is a little information on this here: https://wiki.agiloft.com/display/HELP/Formulas

In your example. If we want to create 2017 from 12345-17-XX we would set a new field (calculated result, text or number) to have the value:
concat(“20”,replace(replace($original_field,"…-",""),"-…",""))

Below is how this formula works:


This formula works only if the structure is always XXXX-XX-XX and only if the year larger than 2000.

Jack,

Thank you for the prompt response and thorough answer. I was able to get the result I wanted using your formula. Thanks again!