extraction de nombre dans une chaine

Sub Bouton1_QuandClic()
Dim tablo As Variant
Dim tablo2 As Variant

tablo = Split(Range('a1'), ',')
tablo2 = Split(tablo(1), 'et')

Range('b1') = tablo(0)
Range('c1') = tablo2(0)
Range('d1') = tablo2(1)
End Sub

Cliquez pour agrandir...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tablo As Variant
Dim tablo2 As Variant

If Target.Address(0, 0) = 'A1' Then

    tablo = Split(Target, ',')
    tablo2 = Split(tablo(1), 'et')

    Range('b1') = tablo(0)
    Range('c1') = tablo2(0)
    Range('d1') = tablo2(1)
End If
End Sub

Cliquez pour agrandir...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Byte
Dim nombre As String
Dim col As Byte

If Intersect(Target, Range('e7:e38')) Is Nothing Then Exit Sub
col = 9
    For i = 1 To Len(Target)
        If IsNumeric(Mid(Target, i, 1)) And IsNumeric(Mid(Target, i + 1, 1)) Then
            nombre = nombre & Mid(Target, i, 1)
        ElseIf IsNumeric(Mid(Target, i, 1)) And Not IsNumeric(Mid(Target, i + 1, 1)) Then
            nombre = nombre & Mid(Target, i, 1)
            Cells(Target.Row, col) = nombre
            nombre = ''
            col = col + 1
        End If
    Next i

End Sub

Cliquez pour agrandir...