Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 182
  • Last Modified:

excel hyperlink

Hi guys
=HYPERLINK("http://www.website.com/ordered_item.asp?item=" & B2 & ,E2 )
I have an XLS spreadsheet that contain a column with the above formula
I need to delete columns B and E and keep the hyperlink functioning
Is there a way to do that using copy & paste Special?
1 Solution
Haris DjulicCommented:
Not clear.. You want to remove the columns B i E from formula just do this : =+HYPERLINK("http://www.website.com/ordered_item.asp?item=")

or something else?

Please provide sample output to be more clear?
Christopher Jay WolffWiggle My Legs, OwnerCommented:

What comes after your link address is the friendly name of your clickable link.  So if you want someone to see "&B2, E2" as the text in your hyperlink cell on which to click, then "&B2, E2" has to be in quotes.

If the text value of Cell B2 is "Click here for items" then then you should delete the & and use...
=HYPERLINK("http://www.website.com/ordered_item.asp?item=", B2) which will allow your hyperlink cell to display "Click here for items" rather than the entire URL as a clickable link.

And if you delete column B, then your hyperlink should still work but the link to click on will simply be a "0" since column B no longer has data in it.

Is this what you need?
I think you currently have it set up the following way:
Column B contains the item link that needs to go into the URL, Column E is a 'friendly' description of the item.

I don't think there is an easy way to do this with copy/paste, however you could use some VBA coding to replace the original Hyperlink:
Public Sub UpdateHyperLinks()
    'Assuming the Hyperlinks are in column A, from A2 to A20
    Const colHyperlink As Integer = 1 'A=1, B=2, etc
    Const colItem As Integer = 2 'linkitem in column B
    Const colFriendly As Integer = 5 'friendly name in column E
    Const rowStart As Integer = 2
    Const rowEnd As Integer = 20
    Const URL As String = "http://www.website.com/ordered_item.asp?item="
        Dim iRow As Integer
        Dim strLinkItem As String
        Dim strFriendlyName As String
        Dim strHyperLink As String
        Dim r As Excel.Range
        Dim strFormula As String
        For iRow = rowStart To rowEnd
            strLinkItem = ActiveSheet.Cells(iRow, colItem)
            strFriendlyName = ActiveSheet.Cells(iRow, colFriendly)
            strHyperLink = URL & strLinkItem
            Set r = ActiveSheet.Cells(iRow, colHyperlink)
            strFormula = "=Hyperlink(""" & strHyperLink & """ , """ & strFriendlyName & """)"
            r.Formula = strFormula
End Sub

Open in new window

This code could be a lot shorter, but I have included some extra constants and variables to illustrate the usage.
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

dory550Author Commented:
Thank you guys
I had a small error in my original question below is the corrected hyperlink function
=HYPERLINK("http://www.website.com/ordered_item.asp?item=" & B2w  ,E2 )
in short
E2=product_Number  B2= product_description
Below is the same hyperlink showing sample contents of  E2 and B2
=HYPERLINK("http://www.website.com/ordered_item.asp?item=" & "8881234"  ,"Duracell AA -4pk  batteries"  )

The description is a hyperlink's friendly text
When clicked the user gets a webpage with price and expanded details and an image
Basically I am trying to create a hyperlink column that will show the contents of E2 and B2
Instead of the variables E2 and B2
Hope this is a little clearer
dory550Author Commented:
the fourth line in my last post should read
=HYPERLINK("http://www.website.com/ordered_item.asp?item=" & B2  ,E2 )
>> I had a small error in my original question below is the corrected hyperlink function
Yeah, I figured that was the case ;) but my solution will still get you what you need.
dory550Author Commented:
Thanks for your help
Your solution is fine

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now