Forum Discussion

luckycarman's avatar
luckycarman
Copper Contributor
Dec 09, 2020
Solved

Seeking help for setting hyperlink as a batch

I've googled so hard and can't find an answer..... I got Column A (name of the link) and Column B (the URL). I need to create a hyperlink cell which i can simply click and jump to the website (see picture).

 

However,

1) i can't do it manually one by one by right click -> hyperlink because there are over 500 links on list.

2) i can't use =hyperlink(B2,A2) as my boss want me to delete column A and B before distributing the list out. (but let me know if i can somehow paste special coz i'm not able to find something works).

3) i can't use third party app because my company doesn't allow installation.

 

Please save me... Learning to use macro is still better to do the 500 entries one by one......

 

excel
Formulas and Functions
Macros and VBA

5 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Dec 09, 2020

    luckycarman 

    Create and run this macro:

    Sub CreateHyperlinks()
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    m = Range("A" & Rows.Count).End(xlUp).Row
    For r = 2 To m
        ActiveSheet.Hyperlinks.Add _
            Anchor:=Range("A" & r), _
            Address:=Range("B" & r).Value, _
            TextToDisplay:=Range("A" & r).Value
    Next r
    Range("B2:B" & m).Clear
    Application.ScreenUpdating = True
End Sub
  • tauqeeracma's avatar
    tauqeeracma
    Iron Contributor
    Dec 09, 2020

    luckycarman Hi

     

    You can achieve your requirements using =CONCATENATE() or Concat() function in given way:

     

     

    I have also attached a sample file, you just need to apply given formulas from this file to your original file then copy and paste column G as 'values' and finally remove spaces from column G (use find & replace option). After this you can easily delete your original columns.

     

    Hope this will help.

     

    Please try on test file first.

     

    Thanks

    Tauqeer

     

    • luckycarman's avatar
      luckycarman
      Copper Contributor
      Dec 09, 2020
      Thank you so much! Can i ask one more question: if there is one more space in column A (e.g. the name is "Microsoft Office"), using find and replace will delete the 2nd space as well, making the name as MicrosoftOffice. Are there quick way to remove the the first space in column G only?

Resources