Turning a customer number in a cell to an external hyperlink

[Excel_Test_21]

each morning i receive a report that has a list of customer job numbers and i am looking for a way to create a macro to have each number converted to a hyperlink that takes me to our company's customer record.

right now i have to copy the number from excel and past it in our company's look up screen to return the results.

the job # will always be in the same column and it comes up in our browser as the location ID in the script below.

for example

13582232​	https://SampleCompanyName/Office/Location/LocationInformation.aspx?locationID=13582232&_FK=&inCurrentCompanyStructureScope=1&hasSecurityRightToModify=1
13582233​	https://SampleCompanyName/Office/Location/LocationInformation.aspx?locationID=
13582234​	https://SampleCompanyName/Office/Location/LocationInformation.aspx?locationID=
13582235​	https://SampleCompanyName/Office/Location/LocationInformation.aspx?locationID=
13582236​	https://SampleCompanyName/Office/Location/LocationInformation.aspx?locationID=

 

[Excel_Test_21]

Just use something like Dropbox, Mega, or any one that allows you to share a file and share the link here.
I assume when you entered the VBA you saved the file with the .xlsm extension.
Click below to download file I used
Hyperlink example

i just realized, our customer numbers will have multiple jobs following for example 12345678-01, 12345678-02. i would need the hyperlink to drop the -01 & -02 to access the company weblink.
let me know your thoughts so i don't have to have an extra column if possible

 

[Kerryx]

Doyou mean the Servive order will have extra on the job number like below, can you give example please as do you want "-01" removed from a specific column or all columns

hyperlinks3.xlsm
	A	B
1	Service order ID	CustomerID
2	123	111111
3	456	222222
4	789	333333
5	11112	444444
6	131415	555555
7	456789	666666
8	6785	777777
9	758764	888888
10	11112-01	444444
11	131415-02	555555
12	456789-11	666666
13	123-13	111111
14	456-05	222222
Sheet1

 

[Kerryx]

Hyperlink 2 column change
See file example attached updated code to remove the numeric extensions in column A and allow hyperlinks to be added to column 2.
One of the more proficent VBA guys may be able to offer a neater code but this works for me.

Sub createLink()

    Dim Rng As Range
    Dim x, y As Integer
    Dim LastRowA, LastRowB As Integer
    LastRowA = Cells(Rows.Count, "A").End(xlUp).Row
    LastRowB = Cells(Rows.Count, "B").End(xlUp).Row
    Set Rng = Range("A2", Range("A2").End(xlDown)) 'This is column with hyphen extensions
    Rng.Replace What:="-*", Replacement:="", LookAt:=xlPart 'Anything with a - , replace it and all after it with nothing
    
    With ActiveWorkbook.Worksheets("Sheet1")
        For x = 2 To LastRowA
        Cells(x, "A").Activate
        .Hyperlinks.Add Anchor:=ActiveCell, Address:="https://SampleCompanyName/Office/Location/LocationInformation.aspx?locationID=" _
        & ActiveCell.Text & "&_FK=&inCurrentCompanyStructureScope=1&hasSecurityRightToModify=1", TextToDisplay:=ActiveCell.Text
        Next x
        
        For y = 2 To LastRowB
        Cells(y, "B").Activate
        .Hyperlinks.Add Anchor:=ActiveCell, Address:="https://SampleColB_name" & ActiveCell.Text & "extra_if_needed", TextToDisplay:=ActiveCell.Text
        Next y
    End With
  End Sub