Extract text and turn into hyper link
Hi Smartsheet Community,
I'm working on a Smartsheet setup where
I have a Jira and Smartsheet connector. Imported Jira fields to Smartsheet. One of the cells is the "Issue Links" where the content typically returns as "mentioned by SHIP-1234" or "dependency of SHIP-1234". I need to extract the SHIP-1234 and turn it into a hyper link.
My goal is to:
- Extract the ticket reference (e.g., SHIP-1234) from the text.
- Convert it into a clickable hyperlink (e.g., SHIP-1234). (example here with a fake URL: https://jira.com/pm/SHIP-1234)
I'm looking for help with the correct formula or workaround to achieve this.
Example Input:
"mentioned by SHIP-1234"
Desired Output: (can be in a new cell)
SHIP-1234
Any advice, tips, or similar experiences would be greatly appreciated!
Thank you in advance for your help!
Best Answer
-
Hello @czheng,
I have something for you that seems to work in a demo below. I would ask @Paul Newcome if there is an better way to achieve this.
From the two examples you have shared, the "Ship-1234" can either be:
- At the end of a string of text or
- In the middle of a string separated by a "," at the end of a SHIP number.
- I am guessing from your second example the number of digits after "Ship-" is not always going to be a 4 digit number.
In the formula below:
- IF the "mentioned by SHIP-123" is in the middle of a string, it will extract the SHIP- and the numbers until the next ",".
- In the cases where you just have "mentioned by SHIP-123" in a cell, it will extract "SHIP-123
- If there is no "SHIP-" found for a given row, it will show blank
- Note:- If you have more than 1 "SHIP-" in a string, it will take the 1st one only.
=IFERROR(IFERROR(MID([Jira Text]@row, FIND("SHIP-", [Jira Text]@row), FIND(",", [Jira Text]@row + " ", FIND("SHIP-", [Jira Text]@row)) - FIND("SHIP-", [Jira Text]@row)), MID([Jira Text]@row, FIND("SHIP-", [Jira Text]@row), FIND(" ", [Jira Text]@row + " ", FIND("SHIP-", [Jira Text]@row)) - FIND("SHIP-", [Jira Text]@row))), "")
To cover for cases where there may not be "SHIP-" found in the Jira text, I have added in IF statement in to the JOIN formula to show "" for the hyperlink, if {Extract SHIP-#] is blank.
=IF(ISBLANK([Extract SHIP-#]@row), "", JOIN("https://jira.com/pm/" + [Extract SHIP-#]@row))
Protonsponge
Answers
-
Hello @czheng,
I was interested by your question and so tried to work out how I might do this myself, however there might be other ways to achieve this than what is proposed below.
If your output from Jira is going to always start with "mentioned by ", we could look to separate the text after the second space.
The formula below will separate the text in bold from your example - mentioned by SHIP-1234
=MID([Jira Text]@row, FIND(" ", [Jira Text]@row, FIND(" ", [Jira Text]@row) + 1) + 1, LEN([Jira Text]@row))
With the "Ship-1234" extracted from your Jira output, you could make use of a JOIN formula to make the hyperlink.
=JOIN("https://jira.com/pm/" + [Extract SHIP-1234]@row)
I don't believe it is possible to have the link display as per your desired output - SHIP-1234. I have asked previously in this post if it was possible to have a shortened version of a link and the feedback from @Paul Newcome was that it isn't possible.
As mentioned above, there may well be a better way to achieve this but hopefully there is something in the above that could be helpful to you in someway,
Protonsponge
-
There is no way to get the display set as something different than the URL itself using formulas, but the API, the premium add-on Bridge, or other third party apps are able to do that.
-
Oh wow ! Thank you. This is a good work around. I can live with two columns and long URL .
-
@czheng - That's great, I am really pleased that worked out for you.
-
@Protonsponge
Thank you. I have a follow up question. I am not very good with smartsheet formular, but i know it can be done
=MID([Jira Text]@row, FIND(" ", [Jira Text]@row, FIND(" ", [Jira Text]@row) + 1) + 1, LEN([Jira Text]@row)) works well if there is just one mentions of the word
Say if the cell in Jira Text is
"dependency of DE-123, mentioned by SHIP-123, also mentioned by RM-123"
I know anything before SHIP doesn't matter but how do I trim off the also by mentioned by Rm-123 parts? I am only interested int the SHIP (giving most time one ticket only relate to one SHIP, not multiple SHIPs)
Thanks in advance. -
Hello @czheng,
I have something for you that seems to work in a demo below. I would ask @Paul Newcome if there is an better way to achieve this.
From the two examples you have shared, the "Ship-1234" can either be:
- At the end of a string of text or
- In the middle of a string separated by a "," at the end of a SHIP number.
- I am guessing from your second example the number of digits after "Ship-" is not always going to be a 4 digit number.
In the formula below:
- IF the "mentioned by SHIP-123" is in the middle of a string, it will extract the SHIP- and the numbers until the next ",".
- In the cases where you just have "mentioned by SHIP-123" in a cell, it will extract "SHIP-123
- If there is no "SHIP-" found for a given row, it will show blank
- Note:- If you have more than 1 "SHIP-" in a string, it will take the 1st one only.
=IFERROR(IFERROR(MID([Jira Text]@row, FIND("SHIP-", [Jira Text]@row), FIND(",", [Jira Text]@row + " ", FIND("SHIP-", [Jira Text]@row)) - FIND("SHIP-", [Jira Text]@row)), MID([Jira Text]@row, FIND("SHIP-", [Jira Text]@row), FIND(" ", [Jira Text]@row + " ", FIND("SHIP-", [Jira Text]@row)) - FIND("SHIP-", [Jira Text]@row))), "")
To cover for cases where there may not be "SHIP-" found in the Jira text, I have added in IF statement in to the JOIN formula to show "" for the hyperlink, if {Extract SHIP-#] is blank.
=IF(ISBLANK([Extract SHIP-#]@row), "", JOIN("https://jira.com/pm/" + [Extract SHIP-#]@row))
Protonsponge
-
Thank you so much. Let me play with these and if there is better way to handle this that would be great. But this is extremely helpful. !!!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 500 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!