topic Re: Remove hyperlink when opening Excel with hyperlinked cells in Discussions

Remove hyperlink when opening Excel with hyperlinked cells

Neo — Thu, 08 Jun 2023 16:38:11 GMT

I am opening (code below) an excel spreadsheet which has got hyperlinked entries in a particular column. Hyperlinks for each entry point to another excel spreadsheet. JMP 16.2 is loading these entries with a blue underline and this is causing me issues in data manipulations downstream.

I want to load the data for this column as Charecter/Norminal. How to achieve this?

Thanks.

Names Default To Here( 1 );
dt1 = Open(
	"/F:/Log 2023.xls",
	Worksheets( "Sheet1" ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 1 ),
		Headers Start on Row( 3 ),
		Data Starts on Row( 4 ),
		Data Starts on Column( 1 ),
		Data Ends on Row( 0 ),
		Data Ends on Column( 0 ),
		Replicated Spanned Rows( 0 ),
		Replicated Spanned Headers( 0 ),
		Suppress Hidden Rows( 0),
		Suppress Hidden Columns( 0 ),
		Suppress Empty Columns( 0 ),
		Treat as Hierarchy( 0 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( "-" )
	)
);
 
dt1 << Set Name( "Log2023" );

Re: Remove hyperlink when opening Excel with hyperlinked cells

jthi — Tue, 07 Feb 2023 14:07:17 GMT

My guess is that you have a file like this:

There might be other easier options, but here is one option to modifying the data.

You can see that column data type is expression and that it has column property.

Open column properties

Remove the column property Event Handler and change the data type to Character and modeling type to nominal

After this you might have to perform some data modification as your data might look something like this:

Re: Remove hyperlink when opening Excel with hyperlinked cells

Neo — Tue, 07 Feb 2023 14:59:52 GMT

@jthi Thanks. I had already tried what you are suggesting. It did not help. Please see below.

This is my spreadsheet (only one row show in the example). Column A has entries which are hyperlinked to a file with name identical to the row entry.

Loading this into JMP gives

Changing the data and modelling type on the column gives me

with the row entry as {"ABC99.0001.01"("ABC99.0001.01.xlsx")}.

Whereas I want only ABC99.0001.01. Nothing extra. How to achieve this?

Re: Remove hyperlink when opening Excel with hyperlinked cells

jthi — Wed, 08 Feb 2023 09:05:57 GMT

Depending on your data use some method to get the values from that column. For example Word or Regex

Word(2, str, "\!"");
Regex(str, "\[\{\"(.+)"\(]\", "\1");

Recode platform also offers Extract Segment which might work.

Re: Remove hyperlink when opening Excel with hyperlinked cells

hogi — Sun, 03 Mar 2024 18:59:44 GMT

I have the opposite issue: I have an excel file with hyperlinks.

When I import the file in Jmp, I just see the text - the hyperlinks are gone.

Re: Remove hyperlink when opening Excel with hyperlinked cells

jthi — Mon, 04 Mar 2024 07:24:36 GMT

Are they hyperlinks built using HYPERLINK function in excel?

Most likely JMP cannot manage that and you would have to either parse the xlsx file yourself, open as zip and you can see it's structure, this can be a lot of work to parse, but maybe you could just parse some parts of it?

or have some sort of a script (powershell/vbscript) to first extract those links and then load that to JMP.

Re: Remove hyperlink when opening Excel with hyperlinked cells

hogi — Mon, 04 Mar 2024 19:15:20 GMT

https://www.excel-university.com/extract-url-from-hyperlink-with-an-excel-formula/

Function URL(Hyperlink As Range)
  URL = Hyperlink.Hyperlinks(1).Address
End Function