Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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.

doc copy hyperlink to another cell 1

Copy hyperlink from once cell to another with VBA code

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • 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.

arrow blue right bubble Copy hyperlink from once cell to another with VBA code

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:

doc copy hyperlink to another cell 02

4. And then click OK, then select the cells that you want to paste the hyperlins only in another dialog box, see screenshot:

doc copy hyperlink to another cell 03

5. And the hyperlink addresses have been copied from the original cells to the specified cells as you need, see screenshot:

doc copy hyperlink to another cell 04

Note: This code also can help you to copy the hyperlinks from one sheet to another sheet as you want.


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom

 

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Gary · 3 days ago
    OK, 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.
    Gary · 3 days ago
    Doesn't work for me; does the fact that I'm still using Excel 2007 matter?
  • To post as a guest, your comment is unpublished.
    Pawel · 3 months ago
    This is awesome and it works. I love copy&pasting other people's code :D
  • To post as a guest, your comment is unpublished.
    Abbie · 4 months ago
    This didn't work for me either.
  • To post as a guest, your comment is unpublished.
    LeZa · 6 months ago
    Lets 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.
    iii · 8 months ago
    Doesn't work.
  • To post as a guest, your comment is unpublished.
    Liz Melbin Tejada · 1 years ago
    Love it. Thank you so muchoooo.
    Love From Dominican Republic :)
  • To post as a guest, your comment is unpublished.
    Passing Stranger · 1 years ago
    Doesn't work.
  • To post as a guest, your comment is unpublished.
    eunice · 2 years ago
    hello.. 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.
    Arpit · 2 years ago
    Hi, 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.
      skyyang · 2 years ago
      Hello, 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.
        jprasad · 2 years ago
        I want to copy links in several sheets of one file to another file