Solved

excel hyperlink

Posted on 2014-10-26
7
163 Views
Last Modified: 2014-10-26
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
Comment
Question by:dory550
7 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
Comment Utility
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
 
LVL 9

Expert Comment

by:Christopher Jay Wolff
Comment Utility
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
 
LVL 5

Accepted Solution

by:
ReneD100 earned 500 total points
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:dory550
Comment Utility
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
 

Author Comment

by:dory550
Comment Utility
oops
the fourth line in my last post should read
=HYPERLINK("http://www.website.com/ordered_item.asp?item=" & B2  ,E2 )
Dory
0
 
LVL 5

Expert Comment

by:ReneD100
Comment Utility
>> 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
 

Author Closing Comment

by:dory550
Comment Utility
ReneD100
Thanks for your help
Your solution is fine
dory
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

With the internet and the ease of information transference, many professional jobs can be done anywhere today.  Why should it make a difference whether an x-ray is read in India or the United States as long as the radiologist is qualified?   Outso…
As freelancing is becoming more and more common in the tech industry, certain obstacles are proving to be a challenge to those who are used to more traditional, structured employment. This article is meant to help identify such obstacles and offer a…
This video teaches the viewer how to align pictures around text while keeping the text properly aligned in the document.
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now