LINQ query to extract lyperlinks from excel

Help Activities
, , , , , , , , , , , , , ,
1

Hi,

Can anyone help me with this?
I want to extract the hyperlinks from Column B and write it in Column C.

Input:
image

Output:
image

Any inputs?

2

@Ray_Sha1

Try using reqd range with raw values or read range workbook without preserve formatting

It would get urls and then filter datatable to have only yrl column and can write back to excel

Cheers

3

I want LINQ to do it to increase the processing speed

4

@Ray_Sha1

Both give you same speed…we are not doing any loops here…we are reading the data, then filter and write back only

in place of filter you would use linq again which has no improvement mostly

cheers

5

Hi @Ray_Sha1 ,

Were you able to read the URLs or when tried to extract using the Read Range activities of both Classic/Modern Excel related activities was the URL value extracted in Datatable?

If you are still looking for extracting URL’s or data from the Hyperlink formula, Maybe the below could help :

=MID(FORMULATEXT(A2),SEARCH("(""",FORMULATEXT(A2))+2,SEARCH(""",",FORMULATEXT(A2))-SEARCH("(""",FORMULATEXT(A2))-2)

image
image1133×173 5.98 KB

The above could be achieved by using a Write Cell Activity with the above formula and then use Auto Fill Range or Fill Range activities to update the remaining cell values.

Using the Means of Read Range with Preserve Formatting or no Preserve Formatting did not seem to work when I performed a test, hence this work around.

@Anil_G , Do let us know if you have any other methods in mind :slight_smile:

Let us know if the above suggested method does not work for your scenario.

1 Like
6

@Ray_Sha1

Please try as @supermanPunch mentioned

My bad that I just checked and I dont see url being extracted

And you can try this formula as well =MID(FORMULATEXT(A2),13,FIND("""",FORMULATEXT(A2),13)-13)

cheers

2 Likes
7

Thanks for the input.
If we want to store store it in the datatable instead of writing it back to the sheet?

8

@Ray_Sha1 ,

I believe here, the First Step would be to use the Formula mentioned above and update the Column using the Excel activities as mentioned.

Then we can use Read Range activity and Read the updated data as Datatable which has the hyperlinks url available in the new column, so that we can access those values from the Datatable.

9

I’m not able to extract. I’m getting empty values.
Is there any other way?

10

@Ray_Sha1 ,

Could you let us know what was the implementation done maybe with screenshots?

11

I get N/A values

12

@Ray_Sha1

Can you show the hyperlink and also the formula you are using

Cheers

13

The ones that you guys have provided.
I’m getting NA values in the excel as well

14

@Ray_Sha1

I agree with that…can you first show what is the hyperlink text you have and also the formula might change depending on the cell numbers so wanted to verify

Cheers

15

@Ray_Sha1 ,

Was a manual check done at first and tested ? We will not be able to help more or faster if the implementation done is not shown ?

Were you able to use Write Cell with the Formula provided and did it give N/A values ?

For your case, The Cell reference maybe B2 instead of A2