=ЛЕВСИМВ(A2;МИН(ЕСЛИОШИБКА(ПОИСК({"0";"1";"2";"3";"4";"5";"6";"7";"8";"9"};A2);99))-1)

Sub test()
    Dim lr&, i&, res$, r&, temp$
    Dim dic1 As Object, dic2 As Object, data, k, login
    Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
    
    Set sh1 = ThisWorkbook.Sheets(1)
    Set sh2 = ThisWorkbook.Sheets(2)
    Set sh3 = ThisWorkbook.Sheets(3)
    
    lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
    Set dic1 = CreateObject("scripting.dictionary")
    Set dic2 = CreateObject("scripting.dictionary")
    data = sh1.Range("a2:c" & lr).Value
    
    For i = 1 To UBound(data)
        With CreateObject("VBScript.RegExp")
            .Global = True
            .Pattern = "\d+"
            res = .Replace(data(i, 1), "")
        End With
        If dic1.exists(res) Then
            dic1(res) = dic1(res) & "," & data(i, 1)
        Else
            dic1(res) = data(i, 1)
        End If
        temp = res & "|" & Trim(data(i, 3))
        If dic2.exists(temp) Then
            dic2(temp) = dic2(temp) & "," & data(i, 1)
        Else
            dic2(temp) = data(i, 1)
        End If
    Next i
    
    sh2.[a2].CurrentRegion.Offset(1).ClearContents
    sh3.[a2].CurrentRegion.Offset(1).ClearContents
    r = 2
    With sh2
        For Each k In dic1
            If InStr(dic1(k), ",") Then
                For Each login In Split(dic1(k), ",")
                    .Cells(r, 1) = login
                    r = r + 1
                Next login
            End If
        Next k
    End With
    r = 2
    With sh3
        For Each k In dic2
            If InStr(dic2(k), ",") Then
                For Each login In Split(dic2(k), ",")
                    .Cells(r, 1) = login
                    .Cells(r, 2) = Split(k, "|")(1)
                    r = r + 1
                Next login
            End If
        Next k
    End With
End Sub