0

I have a table with a bunch of URL's to files on my computer that are in plain text. I want to auto convert all of them into url links so it is easier for me to open the link from Excel rather than copying and pasting into a browser each time. I found a macro from a different post in Word but how can I do it in Excel?

For example:

file:///issues%20apache/jira/browse/CONN-1339
file:///issues%20apache/jira/browse/CONN-1196
file:///issues%20apache/jira/browse/CONN-1115
file:///issues%20apache/jira/browse/CONN-1032
file:///issues%20apache/jira/browse/CONN-1027
file:///issues%20apache/jira/browse/CONN-1024
file:///issues%20apache/jira/browse/CONN-1036
file:///issues%20apache/jira/browse/CONN-994
file:///issues%20apache/jira/browse/CONN-782

should look something like this but with file instead of http and no numbers:

  1. http:///issues%20apache/jira/browse/CONN-1339
  2. http:///issues%20apache/jira/browse/CONN-1196
  3. http:///issues%20apache/jira/browse/CONN-1115
  4. http:///issues%20apache/jira/browse/CONN-1032
  5. http:///issues%20apache/jira/browse/CONN-1027
  6. http:///issues%20apache/jira/browse/CONN-1024
  7. http:///issues%20apache/jira/browse/CONN-1036
  8. http:///issues%20apache/jira/browse/CONN-994
  9. http:///issues%20apache/jira/browse/CONN-782

Update: I have tried =HYPERLINK() but that just gives me error The formula you typed contains an error.

Improve this question
2
  • you can use HYPERLINK function of Excel.
    – Máté Juhász
    Oct 18, 2016 at 18:21
  • Please share the HYPERLINK formula you used.
    – CharlieRB
    Oct 18, 2016 at 18:39

1 Answer 1

Reset to default
1

Here are the steps that should be done with a short visual basic script:

  1. Run through all the cells and look for ones that start with "https"
  2. Cut the contents of the cell
  3. Re-write the contents using the "HYPERLINK" function and include the URL you just cut

It's simply finding the syntax to use to do all of this. If you need help with Visual Basic syntax for Excel, you can go over to StackOverflow.

Improve this answer
4
  • Can you show what the formula looks like once you've added it. It seems like it's a syntax error and not a function error.
    – IronWilliamCash
    Oct 18, 2016 at 18:37
  • You are certainly missing some commas. Try this: =HYPERLINK("file:///issues%20apache/jira/browse/CONN-782")
    – IronWilliamCash
    Oct 18, 2016 at 18:40
  • Also, are you trying to link to a website or an actual file? Because either way your syntax is off
    – IronWilliamCash
    Oct 18, 2016 at 18:42
  • I believe you mean add quotes to put: =HYPERLINK("file:///issues%20apache/jira/browse/CONN-782") the result says cannot find the specified file. I found that by replacing the %20 with a space fixed the problem.
    – Whitecat
    Oct 18, 2016 at 18:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .