Extract a Text String to Remove Unwanted Text from Jira Connector Ticket "Summary"

blspmp

Situation: I have a SmartSheet utilized for Epic tracking of project deliveries. I use Grid and Gantt Views. These sheets are typically 50+ rows long with linked tickets under Jira Epics.

Background: My Jira Ticket Summaries (ticket titles) are consistent and long for SOP purposes.

Ex: "ABC-DEF | Client | Q3 August 2024 | Provide Data + Detail Files to Client"

Problem: I need this entire section of every ticket to be removed to focus on the task level.

For example, I only want to see "Provide Data + Detail Files to Client" on my sheet.

Request: What can I utilize to perform this action?

Paul Newcome

Is it consistent in that you want the rest of the string that comes after the third | every time, or is the number of | variable, or is there a possibility you want to also exclude some text after the indicated portion?

If it is consistently everything after the 3rd | every time, you can use this:

=RIGHT([Column name]@row, LEN([Column Name]@row) - (FIND("~", SUBSTITUTE([Column Name]@row, "|", "~", 3)) + 1))

Paul Newcome

Is it consistent in that you want the rest of the string that comes after the third | every time, or is the number of | variable, or is there a possibility you want to also exclude some text after the indicated portion?

If it is consistently everything after the 3rd | every time, you can use this:

=RIGHT([Column name]@row, LEN([Column Name]@row) - (FIND("~", SUBSTITUTE([Column Name]@row, "|", "~", 3)) + 1))

blspmp

@Paul Newcome, thank you so much!

I am receiving an error of "#cirrcularreference".

This is the screenshot from the sheet:

The column title is "Summary", the field pulled from Jira Connector.

Any help is appreciated.

heyjay

blspmp

Jul 9, 2024

@Paul Newcome, thank you so much!

I am receiving an error of "#cirrcularreference".

This is the screenshot from the sheet:

https://us.v-cdn.net/6031209/uploads/5CKURIHIJKU8/image.png

The column title is "Summary", the field pulled from Jira Connector.

Any help is appreciated.

Create a new column and place the formula on that new column.

It looks like you're pasting this formula into the "Job Number" column, which is referenced as a full column reference in your formula, which is why you're receiving the error (see: #CIRCULAR REFERENCE error.)

Paul Newcome

@BLSRNPMP You are going to need to put the formula in a column that is not being referenced by the formula.

blspmp

I want to thank both @Paul Newcome and @heyjay for the combined assistance on this. It did work. So grateful for this amazing community!

Paul Newcome

Happy to help. 👍️