Structure Formula Column Parsing Text

[Andy Conley]

This was driving me a bit nuts, so I thought I'd share in case it might help others as an example.

We are using Jira with Structure/Structure.Gantt.

There is a custom controlled field that represents our internal company projects that follows this pattern:

<4-8 numbers>:<string>

Examples:

• 1028:My Project
• 2382-2828:Your Project

Use Case:

I simply wanted to display the project name/string without the numbers as a column (or grouping) in my structure. 

Solution:

REPLACE_AT(project,0,SEARCH(":",project,0),"")

Using the docs from ALM's site, I landed on the combination of REPLACE_AT and SEARCH.

REPLACE_AT allows me to remove the characters from the delimiter (:), but since the delimiter position was variable, I was able to use SEARCH to identify it's location.

I really wanted to define the SEARCH portion as a separate variable utilizing WITH, but I couldn't quite get that working.

If there is a more efficient way or suggestions on WITH, please share!

[Stepan Kholodov]

Hi Andy,

You can indeed use with statement here:

with index=SEARCH(":", project,0):
REPLACE_AT(project,0,index,"")

An alternative would be to use

REPLACE(project, "/[0-9:]/", "")

Best regards,
Stepan Kholodov
ALM Works