Option Explicit

Sub ВыделеннуюЯчейкуВмассив()
    ЯчейкуВмассив ActiveCell
End Sub

Sub ЯчейкуВмассив(Ячейка As Range)
    Dim arr As Variant
    arr = СтрокуВмассив(Ячейка.Value)
    If Not IsEmpty(arr) Then
        With Ячейка.Resize(UBound(arr, 1), UBound(arr, 2))
            .NumberFormat = "@"
            .Cells = arr
        End With
    End If
End Sub

Function СтрокуВмассив(Строка As String) As Variant
    If Строка <> "" Then
        Строка = Replace(Строка, "https:", "https#")
        Dim arr As Variant
        Dim brr As Variant
        Dim crr As Variant
        arr = Split(Строка, ";")
        ReDim brr(1 To UBound(arr) + 1, 1 To 2)
        Dim y As Long
        For y = 1 To UBound(brr, 1)
            If arr(y - 1) <> "" Then
                crr = Split(arr(y - 1), ":")
                brr(y, 1) = Trim(crr(0))
                If UBound(crr) > 0 Then
                    brr(y, 2) = Trim(crr(1))
                    brr(y, 2) = Replace(brr(y, 2), "https#", "https:")
                End If
            End If
        Next
        СтрокуВмассив = brr
    End If
End Function

https://e-xcel.ru/index.php/makrosy/124-kak-vstavit-gotovyj-makros-v-rabochuyu-knigu