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.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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.· 3 days 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.· 3 days 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.· 3 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.· 4 months agoThis didn't work for me either.
To post as a guest, your comment is unpublished.· 6 months 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.· 8 months 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.· 1 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.· 2 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.