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?
Best Answer
-
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))
Answers
-
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, 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.
-
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.)...
-
@BLSRNPMP You are going to need to put the formula in a column that is not being referenced by the formula.
-
I want to thank both @Paul Newcome and @heyjay for the combined assistance on this. It did work. So grateful for this amazing community!
-
Happy to help. 👍️
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives