How to copy hyperlink only from one cell to another in Excel?
Supposing, I have a list of values in column A and each cell contains a different hyperlink, now, I want to copy only the hyperlinks without text to another column E as following screenshot shown. May be there is no direct way to solve this job in Excel, but here, I can introduce a VBA code for dealing with it.
To only copy the hyperlink addresses without the text to other cells, the following code can do you a favor, please as this:
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: copy hyperlink only from one cell to another:
Sub CopyHyperlinks() 'Uodateby Extendoffice Dim xSRg As Range Dim xDRg As Range Dim I As Integer Dim xAddress As String On Error Resume Next xAddress = ActiveWindow.RangeSelection.Address Set xSRg = Application.InputBox("Please select the original range you want to copy hyperlinks:", "KuTools For Excel", xAddress, , , , , 8) If xSRg Is Nothing Then Exit Sub Set xDRg = Application.InputBox("Please select the new range you want to paste the hyperlinks only", "KuTools For Excel", , , , , , 8) If xDRg Is Nothing Then Exit Sub Set xDRg = xDRg(1) For I = 1 To xSRg.Count If xSRg(I) <> "" And xDRg.Offset(I - 1) <> "" Then If xSRg(I).Hyperlinks.Count = 1 Then xDRg(I).Hyperlinks.Add xDRg(I), xSRg(I).Hyperlinks(1).Address End If End If Next End Sub
3. And then press F5 key to run this code, a dialog box will pop out to remind you to select the cells which you want to copy the hyperlinks only, see screenshot:
4. And then click OK, then select the cells that you want to paste the hyperlins only in another dialog box, see screenshot:
5. And the hyperlink addresses have been copied from the original cells to the specified cells as you need, see screenshot:
Note: This code also can help you to copy the hyperlinks from one sheet to another sheet as you want.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 8 months agoOK, so you won't publish my comment - which is fair. But if you want me to register and log in, then you need to show me solutions that work, because (on the basis of one non-working instance) I've seen nothing to persuade me that there's any value in registering.
- To post as a guest, your comment is unpublished.· 8 months agoDoesn't work for me; does the fact that I'm still using Excel 2007 matter?
- To post as a guest, your comment is unpublished.· 11 months agoThis is awesome and it works. I love copy&pasting other people's code :D
- To post as a guest, your comment is unpublished.· 1 years agoThis didn't work for me either.
- To post as a guest, your comment is unpublished.· 1 years agoLets say in your cell A1 is hyperlink, so to get the path of the hyperlink just create formula like below:
>> (MID((FORMULATEXT(A1));(FIND("(";(FORMULATEXT(A1)))+2);(FIND(";";(FORMULATEXT(A1)))-1)-(FIND("(";(FORMULATEXT(A1)))+2))) <<
- To post as a guest, your comment is unpublished.· 1 years agoDoesn't work.
- To post as a guest, your comment is unpublished.· 1 years agoLove it. Thank you so muchoooo.
Love From Dominican Republic :)
- To post as a guest, your comment is unpublished.· 2 years agoDoesn't work.
- To post as a guest, your comment is unpublished.· 2 years agohello.. what if i want to copy the hyperlink through vlookup? i already have the formula but when i click the the hyperlink it "cannot open specied file" will appear.
Please help me
- To post as a guest, your comment is unpublished.· 3 years agoHi, what if I want to copy my hyperlink from sheet A cell A5 to Sheet B cell A5?
- To post as a guest, your comment is unpublished.· 3 years agoHello, Arpit:
I have updated the VBA code, now, you can apply it in two worksheets as you need. Please try it, hope it can help you!
- To post as a guest, your comment is unpublished.· 3 years agoI want to copy links in several sheets of one file to another file