Copy Hyperlink to other cell

[PRIMA]

Dear All, how can I copy and paste only the hyperlink of one cell to the other cell with the same worksheet or workbook? I am using Excel 2010. Thanks

 

[PRIMA]

this is what I have in A1 (=HYPERLINK("C:\Users\a.puy\Desktop\Barwa City Project\EOT Claims\EOT Claims JBK\BCP1-GCF-LET-JBK-12-0009 (BMS & BTU Installation Completion of Sections 1-4) -08Apr12-.pdf", "BCP1-GCF-LET-JBK-12-0009 (BMS & BTU Installation Completion of Sections 1-4) -08Apr12-.pdf"))

 

[Andrew Poulsom]

That's not a Hyperlink. It's a formula that returns a Hyperlink.

 

[PRIMA]

I see, sorry, I am not very well verse in this. This is actually coming from ASAP Utilities. If possible do you have any idea on how do the same. Thank you again.

 

[Andrew Poulsom]

Do you want to insert actual Hyperlinks in column B or formulas that use the HYPERLINK function?

 

[PRIMA]

let say, I have in column B1 Text to Display = "BCP1-GCF-LET-JBK-12-0009" what I want if I click the column B1 it will show up the pdf file. my hyperlink is on column A1, so I have to click column A1. my main objectives here is to show only the Text to Display = "BCP1-GCF-LET-JBK-12-0009", instead of this "BCP1-GCF-LET-JBK-12-0009 (BMS & BTU Installation Completion of Sections 1-4) -08Apr12-.pdf" the text to display is too long. whatever is the best to insert whether actual or formula hyperlink it is fine for me Sir. thanks and sorry for that.

 

[Andrew Poulsom]

Why don't you just change your existing formula in A1?

=HYPERLINK("C:\Users\a.puy\Desktop\Barwa City Project\EOT Claims\EOT Claims JBK\BCP1-GCF-LET-JBK-12-0009 (BMS & BTU Installation Completion of Sections 1-4) -08Apr12-.pdf", "BCP1-GCF-LET-JBK-12-0009")

The HYPERLINK function's second argument is the text to display.

 

[PRIMA]

you are exactly right Sir, but I have to do it manually. Can you tell me the easiest way? Thank you again...

 

[Andrew Poulsom]

Can I see some more formulas please to establish a pattern? Or is the text to display always in column B?

 

[PRIMA]

=HYPERLINK("C:\Users\a.puy\Desktop\Barwa City Project\EOT Claims\EOT Claims JBK\BCP1-GCF-LET-JBK-12-0009 (BMS & BTU Installation Completion of Sections 1-4) -08Apr12-.pdf" "BCP1-GCF-LET-JBK-12-0009 (BMS & BTU Installation Completion of Sections 1-4) -08Apr12-.pd <tbody> </tbody>	BCP1-GCF-LET-JBK-12-0009 <tbody> </tbody>
=HYPERLINK("C:\Users\a.puy\Desktop\Barwa City Project\EOT Claims\EOT Claims JBK\BCP1-GCF-LET-JBK-12-0016-QPM (Inadequate Welfare facilities) -13Jun12-.pdf", "BCP1-GCF-LET-JBK-12-0016-QPM (Inadequate Welfare facilities) -13Jun12-.pdf") <tbody> </tbody>	BCP1-GCF-LET-JBK-12-0016-QPM <tbody> </tbody>
=HYPERLINK("C:\Users\a.puy\Desktop\Barwa City Project\EOT Claims\EOT Claims JBK\BCP1-GCF-LET-JBK-12-0021 (Delay in BMS & BTU Meter Works) -18Jul12-.pdf", "BCP1-GCF-LET-JBK-12-0021 (Delay in BMS & BTU Meter Works) -18Jul12-.pdf") <tbody> </tbody>	BCP1-GCF-LET-JBK-12-0021 <tbody> </tbody>
=HYPERLINK("C:\Users\a.puy\Desktop\Barwa City Project\EOT Claims\EOT Claims JBK\BCP1-GCF-LET-JBK-12-0025-QPM (BMS Installation) -13Sep12-.pdf", "BCP1-GCF-LET-JBK-12-0025-QPM (BMS Installation) -13Sep12-.pdf") <tbody> </tbody>	BCP1-GCF-LET-JBK-12-0025-QPM <tbody> </tbody>
=HYPERLINK("C:\Users\a.puy\Desktop\Barwa City Project\EOT Claims\EOT Claims JBK\BCP1-JAM-RLET-JBK-12-0014-QPM (JBK Recovery Plan) -13May12-.pdf", "BCP1-JAM-RLET-JBK-12-0014-QPM (JBK Recovery Plan) -13May12-.pdf") <tbody> </tbody>	BCP1-JAM-RLET-JBK-12-0014-QPM <tbody> </tbody>
=HYPERLINK("C:\Users\a.puy\Desktop\Barwa City Project\EOT Claims\EOT Claims JBK\BCP1-PCF-LET-JBK-12-0002 (Programme Submittal) -08Feb12-.pdf", "BCP1-PCF-LET-JBK-12-0002 (Programme Submittal) -08Feb12-.pdf") <tbody> </tbody>	BCP1-PCF-LET-JBK-12-0002 <tbody> </tbody>
=HYPERLINK("C:\Users\a.puy\Desktop\Barwa City Project\EOT Claims\EOT Claims JBK\BCP1-PCF-LET-JBK-12-0008 (JBK Baseline Programme) -05Apr12-.pdf", "BCP1-PCF-LET-JBK-12-0008 (JBK Baseline Programme) -05Apr12-.pdf") <tbody> </tbody>	BCP1-PCF-LET-JBK-12-0008 <tbody> </tbody>
=HYPERLINK("C:\Users\a.puy\Desktop\Barwa City Project\EOT Claims\EOT Claims JBK\BCP1-PCF-RLET-JBK-12-0005 (Programme Submittal dated 20Feb12) -21Feb12-.pdf", "BCP1-PCF-RLET-JBK-12-0005 (Programme Submittal dated 20Feb12) -21Feb12-.pdf") <tbody> </tbody>	BCP1-PCF-RLET-JBK-12-0005 <tbody> </tbody>
=HYPERLINK("C:\Users\a.puy\Desktop\Barwa City Project\EOT Claims\EOT Claims JBK\BCP1-PCF-RLET-JBK-12-0006 (Monthly Report for January 2012) -21Feb12-.pdf", "BCP1-PCF-RLET-JBK-12-0006 (Monthly Report for January 2012) -21Feb12-.pdf") <tbody> </tbody>	BCP1-PCF-RLET-JBK-12-0006 <tbody> </tbody>
=HYPERLINK("C:\Users\a.puy\Desktop\Barwa City Project\EOT Claims\EOT Claims JBK\BCP1-PCF-RLET-JBK-12-0011 (Monhtly Report March 2012) -18Apr12-.pdf", "BCP1-PCF-RLET-JBK-12-0011 (Monhtly Report March 2012) -18Apr12-.pdf") <tbody> </tbody>	BCP1-PCF-RLET-JBK-12-0011 <tbody> </tbody>

<tbody>
</tbody>

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim Rng As Range
    Dim Cell As Range
    With ActiveSheet
        Set Rng = .Range("A1:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
            For Each Cell In Rng
                With Cell
                    If Left(.Formula, Len("=HYPERLINK")) = "=HYPERLINK" Then
                        .Formula = Left(.Formula, InStr(.Formula, ",")) & """" & .Offset(0, 1).Value & """)"
                    End If
                End With
            Next Cell
    End With
End Sub