Amend hyperlink

Hello community,

Could you please help to solve this issues:

1. The excel input file contains columns with hyperlinks, but when I open the file in the Alteryx, all hyperlinks shown as a text. How to make them visible as a hyperlink in the Alteryx output?

2. Some hyperlinks require amendment, mainly partially removing/deleting the: =HYPERLINK(" from the beginning of the hyperlink statement and from the end: ","TD Chart" . Could you please advise how to cut the hyperlink from the both ends.

Example: =HYPERLINK("http://login.GoodsReviews.com/public/ViewTdData/?goodsid=1907276486","TD Chart")

Thanks,

Anar

Preparation

Accepted answers

Jonathan-Sherman

Hi @adombayeva,

You could use a regex tool to solve this using an expression of:

=HYPERLINK\("(.*?)","TD Chart"\)

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

20200123 - ParseURLs.yxmd

danilang

hi @adombayeva

This was a fun early morning exercise.

The key to this workflow is to import the .xlsx as zip file, and then read the contained xml files as csv with no delimiters, no field names and a long field length

Once you get worksheet and shared string xml files, its just a matter of parsing them to extract the rows and columns, and joining the shared strings to get the values. This workflow has an extra step of parsing the hyperlink to extract the target.

The results look like this

Note: The Attached package contains your sample excel file with the Alteryx Output tab removed. This was my first time parsing the internal structure of an Excel file and I wanted to start with a simpler file

Dan

GetExcelHyperLinks.yxzp

All comments

Jonathan-Sherman

Hi @adombayeva,

You could use a regex tool to solve this using an expression of:

=HYPERLINK\("(.*?)","TD Chart"\)

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

20200123 - ParseURLs.yxmd

adombayeva

Hello @Jonathan-Sherman,

Thanks for prompt respond,

Sorry if I was not clear before,

Your solution is exact for issue #2, but before that I wanted to make the hyperlink visible as a hyperlink but not as a text (TD Chart or TD Data under the columns "TD Chart" & "TD Data" in the Alteryx when I upload the Excel file into it.

Please find in the attachment:

1. Excel input file: please see the Alteryx Input Tab (this is how the input file looks like), Alteryx Output Tab (this is how the alteryx output file should look like afterwards).

PNG file: When I upload Excel as an input file and download Alteryx output file, none of them do not showcase the hyperlinks at all for me to make a corrections.

Kind regards

Alteryx.xlsx

Alteryx insights.PNG

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703