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

x
  • 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
Now
I need to delete columns B and E and keep the hyperlink functioning
Is there a way to do that using copy & paste Special?
thanks
dory
0
dory550
Asked:
dory550
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?
0
 
Christopher Jay WolffWiggle My Legs, OwnerCommented:
Hi.

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?
0
 
ReneD100Commented:
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
        Next
   
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.
0
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
Sorry
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
Thanks
Dory
0
 
dory550Author Commented:
oops
the fourth line in my last post should read
=HYPERLINK("http://www.website.com/ordered_item.asp?item=" & B2  ,E2 )
Dory
0
 
ReneD100Commented:
>> 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.
0
 
dory550Author Commented:
ReneD100
Thanks for your help
Your solution is fine
dory
0

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