XL 2016 Extraire chaine de caractères

Sub Sig()
    Dim i As Integer
    Dim dateText As String
    Dim correctedDate As String
  
    For i = 2 To 4
        ' Récupère le texte de la date
        dateText = Split(Cells(i, 1), "SIG")(0)
      
        ' Corrige les erreurs communes dans le nom du mois
        correctedDate = CorrigerMois(dateText)
      
        ' Essaye de convertir et de formater le texte corrigé en date
        On Error Resume Next
        Cells(i, 3).Value = Format(CDate(correctedDate), "mmmm-yy")
        If Err.Number <> 0 Then
            ' Si la conversion échoue, corrige le texte original dans la cellule source
            Cells(i, 1).Value = Replace(Cells(i, 1).Value, dateText, correctedDate)
            Cells(i, 3).Value = Format(CDate(CorrigerMois(correctedDate)), "mmmm-yy")
        End If
        On Error GoTo 0
    Next i
End Sub

Function CorrigerMois(dateText As String) As String
    ' Remplace les caractères incorrects par les bons
    dateText = Replace(dateText, "Decembre", "Décembre")
    dateText = Replace(dateText, "Fevrier", "Février")
    dateText = Replace(dateText, "Aout", "Août")
  
    ' Ajoutez d'autres remplacements si nécessaire
  
    ' Retourne le texte corrigé
    CorrigerMois = dateText
End Function

Sub Extraire_light()
Dim t, d_l&
d_l = Cells(Rows.Count, 1).End(xlUp).Row
t = Array(Array(1, 1), Array(2, 9))
Columns("A:A").Replace What:=" SIG - ", Replacement:="$", LookAt:=xlPart, SearchOrder:=xlByRows
Range("A2:A" & d_l).TextToColumns Destination:=Range("B2"), DataType:=1, Other:=-1, OtherChar:="$", FieldInfo:=t
End Sub

Sub Sig()
    For i = 2 To 4: Cells(i, 2) = Split(Cells(i, 1), "SIG")(0): Next i
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
With Range("B2:B" & Cells.SpecialCells(xlCellTypeLastCell).Row)
    Application.EnableEvents = False
    .FormulaR1C1 = "=IFERROR(LEFT(RC[-1],SEARCH(""SIG"",RC[-1])-1),"""")"
    .Value = .Value 'supprime les formules
    Application.EnableEvents = True
End With
End Sub

Function DebutEnDAte(ByVal xtxt)
Const Mois = "ja f ar av ai in il ao pt ct ov ce"
Dim t, i
   DebutEnDAte = ""
   t = Split(Application.Trim(xtxt))
   i = Application.Match(True, Application.IsNumber(Application.Search(Split(Mois), t(0))), 0)
   If IsError(i) Then Exit Function
   On Error Resume Next
   DebutEnDAte = CDate(i & "/" & t(1))
End Function

Private Sub Worksheet_Change(ByVal Target As Range)
With Range("B2:B" & Cells.SpecialCells(xlCellTypeLastCell).Row)
    Application.EnableEvents = False
    .FormulaR1C1 = "=IFERROR(--LEFT(RC[-1],SEARCH(""SIG"",RC[-1])-1),"""")"
    .Value = .Value 'supprime les formules
    .NumberFormat = "mmmm * yyyy" 'format Date
    .Columns.AutoFit 'ajustement largeur
    Application.EnableEvents = True
End With
End Sub