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