SOLVED

Seeking help for setting hyperlink as a batch

%3CLINGO-SUB%20id%3D%22lingo-sub-1971479%22%20slang%3D%22en-US%22%3ESeeking%20help%20for%20setting%20hyperlink%20as%20a%20batch%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1971479%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20googled%20so%20hard%20and%20can't%20find%20an%20answer.....%20I%20got%20Column%20A%20(name%20of%20the%20link)%20and%20Column%20B%20(the%20URL).%20I%20need%20to%20create%20a%20hyperlink%20cell%20which%20i%20can%20simply%20click%20and%20jump%20to%20the%20website%20(see%20picture).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%3C%2FP%3E%3CP%3E1)%20i%20can't%20do%20it%20manually%20one%20by%20one%20by%20right%20click%20-%26gt%3B%20hyperlink%20because%20there%20are%20over%20500%20links%20on%20list.%3C%2FP%3E%3CP%3E2)%20i%20can't%20use%20%3Dhyperlink(B2%2CA2)%20as%20my%20boss%20want%20me%20to%20delete%20column%20A%20and%20B%20before%20distributing%20the%20list%20out.%20(but%20let%20me%20know%20if%20i%20can%20somehow%20paste%20special%20coz%20i'm%20not%20able%20to%20find%20something%20works).%3C%2FP%3E%3CP%3E3)%20i%20can't%20use%20third%20party%20app%20because%20my%20company%20doesn't%20allow%20installation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20save%20me...%20Learning%20to%20use%20macro%20is%20still%20better%20to%20do%20the%20500%20entries%20one%20by%20one......%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1971479%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1971695%22%20slang%3D%22en-US%22%3ERe%3A%20Seeking%20help%20for%20setting%20hyperlink%20as%20a%20batch%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1971695%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F896172%22%20target%3D%22_blank%22%3E%40luckycarman%3C%2FA%3E%26nbsp%3BHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20achieve%20your%20requirements%20using%26nbsp%3B%3DCONCATENATE()%20or%20Concat()%20function%20in%20given%20way%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22tauqeeracma_1-1607527568887.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F239052iEA022D347DDA4FB6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22tauqeeracma_1-1607527568887.png%22%20alt%3D%22tauqeeracma_1-1607527568887.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20also%20attached%20a%20sample%20file%2C%20you%20just%20need%20to%20apply%20given%20formulas%20from%20this%20file%20to%20your%20original%20file%20then%20copy%20and%20paste%20column%20G%20as%20'values'%20and%20finally%20remove%20spaces%20from%20column%20G%20(use%20find%20%26amp%3B%20replace%20option).%20After%20this%20you%20can%20easily%20delete%20your%20original%20columns.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20will%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EPlease%20try%20on%20test%20file%20first.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1971712%22%20slang%3D%22en-US%22%3ERe%3A%20Seeking%20help%20for%20setting%20hyperlink%20as%20a%20batch%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1971712%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F896172%22%20target%3D%22_blank%22%3E%40luckycarman%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECreate%20and%20run%20this%20macro%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20CreateHyperlinks()%0A%20%20%20%20Dim%20r%20As%20Long%0A%20%20%20%20Dim%20m%20As%20Long%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20m%20%3D%20Range(%22A%22%20%26amp%3B%20Rows.Count).End(xlUp).Row%0A%20%20%20%20For%20r%20%3D%202%20To%20m%0A%20%20%20%20%20%20%20%20ActiveSheet.Hyperlinks.Add%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20Anchor%3A%3DRange(%22A%22%20%26amp%3B%20r)%2C%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20Address%3A%3DRange(%22B%22%20%26amp%3B%20r).Value%2C%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20TextToDisplay%3A%3DRange(%22A%22%20%26amp%3B%20r).Value%0A%20%20%20%20Next%20r%0A%20%20%20%20Range(%22B2%3AB%22%20%26amp%3B%20m).Clear%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

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......

 

5 Replies

@luckycarman Hi

 

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

tauqeeracma_1-1607527568887.png

 

 

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 

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
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?
It works super well! Thanks a lot!
best response confirmed by luckycarman (New Contributor)
Solution

@luckycarman 

I 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