How do I parse a url?

[alphacp]

Hi, I have a field that is simply a url. The url is a link to a ticket so something like “https:///mysystem.com/tickets/1234567” and I would like to be able to refer to the ticket number itself as part of a virtual column.

I was thinking that I could use Split() to parse the url in to a list and then use Index() to somehow reference the last item in the list but this has not worked so far. I tested with just trying to return the first part (i.e., “https:”) using this expression

INDEX(SPLIT([Ticket],"/"),1)

Any ideas here please?

BTW, the ticket number is not a consistent length!

[Suvrutt_Gurjar]

May be your could try

SUBSTITUTE([Column Containing Ticket URL], LEFT([Column Containing URL], (FIND(“tickets/”, [Column Containing URL])+7)),"")

[Column Containing Ticket URL] is assumed to be text type and other string except ticket number is assumed to be consistent.

[alphacp]

Thanks for the idea. I am not yet sure of the any of the url will be consistent. I would prefer to assume not but this is making it harder. I am not sure why my expression did not at least return “https:”

[alphacp]

Made some progress. Need to convert url to text first:

INDEX(SPLIT(TEXT([Ticket]),"/"),1)

Now I just need to work out the size of the list so I can take the last item

[alphacp · Accepted solution]

Bingo… INDEX(SPLIT(text([Ticket]),"/"),COUNT(SPLIT(text([Ticket]),"/")))

[Suvrutt_Gurjar]

alphacp:

Bingo… INDEX(SPLIT(text([Ticket]),"/"),COUNT(SPLIT(text([Ticket]),"/")))

Excellent !

[Suvrutt_Gurjar]

Great to know that. Please do try on those lines.

With expressions there are always multiple possibilities to improve and optimize.

[alphacp]

After all that I forgot that I have never been able to export virtual fields to a csv so it was a waste of time. Just to explain, this is a quick task list and I just wanted to export the list to send out as a status update and include the ticket numbers but when I create an action to export a view as CSV, I never get the values from the virtual columns. Not sure if this is intentional or not but it has never worked for me in any of my prototypes

[Koichi_Tsuji]

Appshee export to CSV action should export all the fields, both physical and virtual column. Not sure why it wont with your app. If it persists, I suggest you go and take route to Support@appsheet.com