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

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?

Find more posts tagged with

Jira Connector

Filters

Accepted answers

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))

All comments

Paul Newcome

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

https://community.smartsheet.com/discussion/comment/444203#Comment_444203

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. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.