Sub Convertir()
Dim tablo, i&, x$, j%, y$, test As Boolean, ncol%, s
With Range("B22", Range("B" & Rows.Count).End(xlUp))
If .Row < 22 Then Exit Sub 'si le tableau est vide
tablo = .Resize(, 2) 'matrice, plus rapide, au moins 2 éléments
'---traitement des espaces---
For i = 1 To UBound(tablo)
x = Application.Trim(tablo(i, 1)) 'SUPPRESPACE
For j = 1 To Len(x)
If Mid(x, j, 1) = " " Then
y = Mid(x, j + 1, 1)
test = False
If j > 9 Then test = Mid(x, j - 9, 9) = "catégorie"
If y <> UCase(y) Or y = "(" Or Mid(x, j, 6) = " Insee" Or test Then _
x = Left(x, j - 1) & Chr(160) & Mid(x, j + 1) 'insertion d'un espace insécable
End If
Next j
tablo(i, 1) = x
Next i
.Value = tablo 'restitution
'---conversion---
ncol = Evaluate("MAX(LEN(" & .Address & ")-LEN(SUBSTITUTE(" & .Address & ","" "",)))") + 1 'nombre de colonnes
ReDim resu(1 To UBound(tablo), 1 To ncol)
For i = 1 To UBound(tablo)
s = Split(tablo(i, 1)) 'l'espace est le séparateur
For j = 0 To UBound(s)
If IsDate(s(j)) Then resu(i, j + 1) = CDate(s(j)) Else resu(i, j + 1) = s(j)
Next j, i
.Resize(i - 1, ncol) = resu 'restitution
.Columns(1).HorizontalAlignment = xlCenter
.EntireRow.Columns.AutoFit 'ajustement largeur
End With
End Sub