XL 2016 format texte personnalisé

=GAUCHE(M21;8)&"-"&DROITE(M21;2)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tablo, i&, x$, chiffres$, lettres$, j%, y$
With Intersect(UsedRange.EntireRow, [A:A])
    tablo = .Resize(, 2) 'matrice, plus rapide, au moins 2 éléments
    For i = 2 To UBound(tablo)
        x = tablo(i, 1)
        chiffres = "": lettres = ""
        For j = 1 To Len(x)
            y = Mid(x, j, 1)
            If y Like "#" Then
                chiffres = chiffres & y
            ElseIf UCase(y) Like "[A-Z]" Then
                lettres = lettres & UCase(y)
            End If
        Next j
        If x = "" Then tablo(i, 1) = "" Else tablo(i, 1) = Format(Val(Left(chiffres, 8)), "0000000") & "-" & Left(Left(lettres, 2) & "??", 2)
    Next i
    '---restitution---
    Application.EnableEvents = False
    .Value = tablo
    Application.EnableEvents = True
End With
End Sub