Solved

excel hyperlink

Posted on 2014-10-26
7
176 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40405024
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
ID: 40405118
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
ID: 40405261
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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

Author Comment

by:dory550
ID: 40405271
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
ID: 40405279
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
ID: 40405295
>> 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
ID: 40405532
ReneD100
Thanks for your help
Your solution is fine
dory
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
Gain an elementary understanding of Blockchain technology.
This video teaches the viewer how to align pictures around text while keeping the text properly aligned in the document.
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…

695 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