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!

Find more posts tagged with

Accepted answers

Protonsponge

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

All comments

Protonsponge

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

Paul Newcome

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.

czheng

Oh wow ! Thank you. This is a good work around. I can live with two columns and long URL .

Protonsponge

@czheng - That's great, I am really pleased that worked out for you.

czheng

@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.

Protonsponge

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.

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

czheng

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. !!!!

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, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.