Removing and Adding Characters from Strings in VBA + add and open this to a Sharepoint URL

Thanks a lot JohnTopley, your help is much appreciated.
The output is correct!

I have two questions left for my problem which I cant seem to figure out.

Is it possible to translate this formula into a VBA string which works for a selected column range instead of inserting this in seperate cells in the sheet itself?

Also how do I create a hyperlink from the cells output? I cant figure out how to do this with a cell containing a formula.

I have inserted a file with the data in column A and the button in Collumn B

The VBA Code is added under the VBA Application / Excel Objects / Sheet1

I have been trying for a week and figure out how I am able to get the follow code to work but it doesn't

On cell activation in collumn B it needs to run the following Macro: ("https://google.com" & "/100" & Substitute(Substitute(Substitute(Substitute(Substitute(A2, "ABC", ""), "-1", ""), "-2", ""), "-3", ""), "-4", ""))

Option Explicit
Private fso As Object

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("B3:B5")) Is Nothing Then
             Application.Run ("https://google.com" & "/100" & Substitute(Substitute(Substitute(Substitute(Substitute(A2, "ABC", ""), "-1", ""), "-2", ""), "-3", ""), "-4", ""))
        End If
    End If
End Sub

Is there anyone who could point me in the right direction with this issue?

I am getting closer!

I am using the following code now

Option Explicit
Private fso As Object

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("B2:B1000")) Is Nothing Then
             Target = ("https://google.com" & "/100" & Replace(Replace(Replace(Replace(Replace(Target.Offset(0, -1), "ABC", ""), "-1", ""), "-2", ""), "-3", ""), "-4", ""))
        End If
    End If
End Sub

When I click the button the cell is showing the right output but there is no Hyperlink output.

The last thing which is needed is to add something to the VBA code that opens the hyperlink output upon click and activating the above VBA code

try

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim x
    If Target.Count = 1 Then
        If Not Intersect(Target, Range("B2:B1000")) Is Nothing Then
            x = GetNum(Target(, 0))
            If x <> "" Then Target = "https://google.com" & "/100" & x
        End If
    End If
End Sub

Function GetNum(ByVal txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = "\d+(?=(\-|$))"
        If .test(txt) Then GetNum = .Execute(txt)(0)
    End With
End Function

The code provided in the latest reply also does the trick by creating the right output upon activating the cell (see latest excel file attachment)

Sub Open_SelectedTextlinks()
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim x
    If Target.Count = 1 Then
        If Not Intersect(Target, Range("B2:B1000")) Is Nothing Then
            x = GetNum(Target(, 0))
            If x <> "" Then Target = "https://google.com" & "/100" & x
        End If
    End If
End Sub

Function GetNum(ByVal txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = "\d+(?=(\-|$))"
        If .test(txt) Then GetNum = .Execute(txt)(0)
    End With
End Function

I have been reading a lot and trying to solve the last step in my problem which is the following.
After selecting the cell and creating the right output it also needs to open this output in the default browser.

I tried to add the following code on top of the code as above
But I havent been able to get it to work.

Sub Open_SelectedTextlinks()
    Dim c As Range

    If Not TypeOf Selection Is Range Then Exit Sub
    For Each c In Selection.Cells
        If c.Hyperlinks.Count = 0 Then
            ActiveSheet.Hyperlinks.Add Anchor:=c, _
                Address:="http://" & c.Value 'Depending on the content of your cell, remove the "http://" & part
        End If
        c.Hyperlinks(1).Follow
    Next
End Sub

Is there anyone who can point me in the right direction?

            If x <> "" Then Target = "https://google.com" & "/100" & x

            If (x <> "") * (Target.Hyperlinks.Count = 0) Then Me.Hyperlinks.Add Target, "https://google.com/100" & x

Thanks for all the help, much appreciated.

Final code that solved my problem:

Sub Open_SelectedTextlinks()
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim x
    If Target.Count = 1 Then
        If Not Intersect(Target, Range("B2:B1000")) Is Nothing Then
            x = GetNum(Target(, 0))
              If (x <> "") * (Target.Hyperlinks.Count = 0) Then Me.Hyperlinks.Add Target, "https://google.com/100" & x
        End If
    End If
End Sub

Function GetNum(ByVal txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = "\d+(?=(\-|$))"
        If .test(txt) Then GetNum = .Execute(txt)(0)
    End With
End Function