How to check if a excel column contains hyperlink

Hi Everyone,

I would like to check if a specific column contains hyperlink.This coloum has 72 rows, some of the excel files sometimes contain hyperlinks and some files not.Im able to remove the hyperlinks for the files that contain hyperlinks.

Hello @tebogo_Moloto :wave:

How does the hyperlink in the column look? Does it have the prefix https://?

If so, you can do this:

yourDT.AsEnumerable().Any(Function(x) x(“Column1”).ToString.Contains(“https://”))

It will return true if any of the rows has the value https://

Cheers!

2 Likes

Hello @monsieurrahul,

Thanks for the respond, the hyperlinks are all emails with different domains for example(blackdear.ptyltd@gmail.com,wayne@fercor.co.za,nnaude@telkomsa.net).The hyperlinks are in one column the column name is email addresses.

If you read the data using ‘read range’ from an excel application scope, it will only return the values as string, nog the formulas behind it.

Once you have that data in a regular datatable, you can use regular expressions to validate the format if it is a valid email, url or other.

If your goal is that all email adresses in the cells should or shouldn’t be formatted as hyperlink you can simply overwrite the cells with the format you want.

Adding a plain text email adress: use a simple write cell or write range.
Adding it as hyperlink use a write cell with this value: “=hyperlink(‘display text’,‘hyperlink/email’)”

2 Likes

Good day @Jeroen_Van_Loon1

How do I validate the format if it is a valid email, url or other using regular expression?
The email,url or other is in a excel sheet in one column .example(blackdear.ptyltd@gmail.com
,nnaude@telkomsa.net,accessp@webmail.co.za and Fixed Retentions Escalation)

Regards,
Tebogo Moloto