utilisez la fonction Right () et obtenez 6 caractères les plus à droite. par exemple:

Dim cell As Range
For Each cell In Range("B2:D2") ' change "B2:D2" to your actual range woth values
    Debug.Print Right(cell.Value, 6)
Next

où cell est une variable Range adressant une cellule pertinente

par exemple

 Right(cell.Value, 6)

Tenez compte de la fonction définie par l'utilisateur suivante:

Public Function GetNumbers(s As String) As String
    Dim L As Long, i As Long, wf As WorksheetFunction
    Set wf = Application.WorksheetFunction

    L = Len(s)
    For i = 1 To L
        If Mid(s, i, 1) Like "[A-Z]" Or Mid(s, i, 1) = "," Then Mid(s, i, 1) = " "
    Next i

    GetNumbers = wf.Trim(s)

End Function

Tous les nombres seront renvoyés sous forme de chaîne séparée par des espaces

Si "E", "W", "F" et "S" sont les seules lettres dont vous devez vous débarrasser, vous pouvez éviter VBA et utiliser la fonction SUBSTITUTE ():

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"E",""),"W",""),"F",""),"S",""),",",""))

p>

Si vous avez Office 365, vous pouvez utiliser cette formule matricielle:

=TEXTJOIN(" ",TRUE,IF((ISNUMBER(--MID(A1,ROW($XFD$1:INDEX(XFD:XFD,LEN(A1)-5)),6)))*(NOT(ISNUMBER(--MID(A1&";",ROW($XFD$1:INDEX(XFD:XFD,LEN(A1)-5)),7)))),MID(A1,ROW($XFD$1:INDEX(XFD:XFD,LEN(A1)-5)),6),""))

Étant une formule matricielle, elle doit être confirmée avec Ctrl-Maj-Entrée au lieu de Entrée lorsque vous quittez le mode d'édition. p>

NDIGITS (UDF)

Formule Excel

'******************************************************************************
' Purpose:    From a string, returns digit groups (numbers) in a delimited
'             string.
' Inputs
'   SourceString    - Required. The string to be checked for digits.
'   NumberofDigits  - Optional. The number of digits in digit groups. If 0,
'                     all digit groups are returned. Default: 0.
'   TargetDelimiter - Optional. The delimiter of the returned string.
'                     Default: " " (space).
'******************************************************************************
Function NDigits(ByVal SourceString As String, _
        Optional ByVal NumberOfDigits As Long = 0, _
        Optional ByVal TargetDelimiter As String = " ") As String

    Dim i As Long         ' SourceString Character Counter
    Dim strDel As String  ' Current Target String

    ' Check if SourceString is empty (""). Exit if. NDigits = "".
    If SourceString = "" Then Exit Function

    ' Loop through characters of SourceString.
    For i = 1 To Len(SourceString)
        ' Check if current character is not a digit (#), then replace with " ".
        If Not Mid(SourceString, i, 1) Like "#" Then _
                Mid(SourceString, i, 1) = " "
    Next

    ' Note: While VBA's Trim function removes spaces before and after a string,
    '       Excel's Trim function additionally removes redundant spaces, i.e.
    '       doesn't 'allow' more than one space, between words.
    ' Remove all spaces from SourceString except single spaces between words.
    strDel = Application.WorksheetFunction.Trim(SourceString)

    ' Check if current TargetString is empty (""). Exit if. NDigits = "".
    If strDel = "" Then Exit Function

    ' Replace (Substitute) " " with TargetDelimiter if it is different than
    ' " " and is not a number (#).
    If TargetDelimiter <> " " And Not TargetDelimiter Like "#" Then
        strDel = WorksheetFunction.Substitute(strDel, " ", TargetDelimiter)
    End If

    ' Check if NumberOfDigits is greater than 0.
    If NumberOfDigits > 0 Then

        Dim vnt As Variant  ' Number of Digits Array (NOD Array)
        Dim k As Long       ' NOD Array Element Counter

        ' Write (Split) Digit Groups from Current Target String to NOD Array.
        vnt = Split(strDel, TargetDelimiter)
        ' Reset NOD Array Element Counter to -1, because NOD Array is 0-based.
        k = -1
        ' Loop through elements (digit groups) of NOD Array.
        For i = 0 To UBound(vnt)
            ' Check if current element has number of characters (digits)
            ' equal to NumberOfDigits.
            If Len(vnt(i)) = NumberOfDigits Then
               ' Count NOD Array Element i.e. prepare for write.
               k = k + 1
               ' Write i-th element of NOD Array to k-th element.
               ' Note: Data (Digit Groups) are possibly being overwritten.
               vnt(k) = vnt(i)
            End If
        Next
        ' Check if no Digit Group of size of NumberOfDigits was found.
        ' Exit if. NDigits = "".
        If k = -1 Then Exit Function
        ' Resize NOD Array to NOD Array Element Count, possibly smaller,
        ' due to fewer found Digit Groups with the size of NumberOfDigits.
        ReDim Preserve vnt(k)
        ' Join elements of NOD Array to Current Target String.
        strDel = Join(vnt, TargetDelimiter)
    End If

    ' Write Current Target String to NDigits.
    NDigits = strDel

End Function
'******************************************************************************
' Remarks:    A digit group are consecutive numbers in the string e.g.
'             in the string "123 sdf jk 23 4" there are three digit groups:
'             The 1st is 123 with NumberOfDigits = 3, the 2nd is 23 with
'             NumberOfDigits = 2 and finally 4 with NumberOfDigits = 1. Since
'             they all have a different number of digits, all will be returned
'             if NumberOfDigits is 0 or omitted, otherwise only one will be
'             returned.
'******************************************************************************

Exemple de données

p>

Code VBA

=NDIGITS($A1,6)

une légère variation de la réponse de Gary's Student:

Public Function GetNumbers2(s As String) As String
    Dim i As Long, elem As Variant

    For Each elem In Split(s, ",")
        For i = 1 To Len(elem)
            If Mid(elem, i, 1) Like "[0-9]" Then Exit For
        Next i
        GetNumbers2 = GetNumbers2 & " " & Application.WorksheetFunction.Trim(Mid(elem, i))
    Next
    GetNumbers2 = Trim(GetNumbers)
End Function

Cette réponse n'est pas meilleure que les autres avec des scores positifs, mais je préfère utiliser Codes ASCII pour la gestion des caractères dans une chaîne. Cela permet aux plages de s'organiser proprement avec Select Statements . Ceci est particulièrement utile pour rejeter les personnages d'utilisateurs non avertis comme mes parents (je n'ai pas nommé leur petit-fils "4").

Voici un UDF qui fonctionnerait pour l'OP, mais montre également comment on pourrait tirer parti de la fonction VBA Asc combinée avec une instruction select pour la manipulation, les majuscules / minuscules ou tout autre caractère spécifique :

Public Function GiveTheNumbers(theINPUT As String) As String
Dim p As Long, aCode As Long

For p = 1 To Len(theINPUT)

    aCode = Asc(Mid(theINPUT, p, 1)) 'converts string to an ascii integer

    Select Case aCode

        '32 is the ascii code for space bar. 48 to 57 is zero to nine.
        Case 32, 48 To 57
            GiveTheNumbers = GiveTheNumbers & Chr(aCode) 'Chr() converts integer back to string


        'the rest of these cases are not needed for the OP but I'm including for illustration
        Case 65 To 90
            'all upper case letters

        Case 97 To 122
            'all lower case letters

        Case 33, 64, 35, 36, 37, 42
            'my favorite characters of: !@#$%*

        Case Else
            'anything else

    End Select

Next p

End Function