CODEWITHSUNDEEP
excelhyperlink
user3145075
user3145075

Reputation: 31

Create Excel Hyperlinks which include data from cells B2-B10000 (or so)

I'm learning mandarin Chinese and study from an excel file with over 10,000 vocabulary words. To view example sentences easily, I want to place my own 10,000 hyperlinks to http://www.purpleculture.net/dictionary-details/?word=%E4%B8%80, except replace the content after "?word=" with each of the 10,000 characters in rows B2-B10002.

How do I do so?

Upvotes: 1

Views: 96

Answers (2)

Maher Fattouh
Maher Fattouh

Reputation: 1860

Here's the solution

Type this in C2

=HYPERLINK("http://www.purpleculture.net/dictionary-details/?word="&ENCODEURL(B2))

This Formuala will encode the content of B2 to make sure it's URL friendly using "ENCODEURL" Available in Excel 2013 or later

Assuming B2 = "Value 1" this cell (C2) will be

http://www.purpleculture.net/dictionary-details/?word=Value%201

Copy this cell and paste it on you 1000+ cells and it'll automatically adjust the row number

For older Versions of Excel You can use

=HYPERLINK("http://www.purpleculture.net/dictionary-details/?word="&(B2))

and if it doesn't work (your browser doesn't auto encode) you might have to encode the text your self using any tool that support multiline encoding e.g. Text Fixer

paste the encoded text in your worksheet and adjust the formula to point to it, just edit the (B2) part

Upvotes: 1

Yannis P.
Yannis P.

Reputation: 2765

Entering the formula =HYPERLINK(B2;RIGHT(B2;LEN(B2)-FIND("=";B2))) into C2 and copying it down to C10002 would do the trick for you.

Now if you want the URL entities to be decoded you will need to apply perhaps another function to the part RIGHT(B1;LEN(B1)-FIND("=";B1)) but as far as I know there is no such thing in Excel

Upvotes: 1

Related Questions