Dec 09 2020 06:53 AM
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......
Dec 09 2020 07:30 AM
@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
Dec 09 2020 07:35 AM
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
Dec 09 2020 08:50 AM
Dec 09 2020 08:51 AM
Dec 09 2020 09:02 AM
SolutionI have modified the file and used '|' instead of space now you need to replace it.
Please refer the attached revised file in case you opt for non-VBA solution.
Thanks
Tauqeer
Dec 09 2020 09:02 AM
SolutionI have modified the file and used '|' instead of space now you need to replace it.
Please refer the attached revised file in case you opt for non-VBA solution.
Thanks
Tauqeer