How to retrieve the all six digit numbers from the exact cell?

Question

For example, our cell contains:

EWFS 410461, 501498, EFW406160

So, I need the formula that gets back with

410461 501498 406160

Answer 1

This answer isn't better than the others with positive scores, but I prefer using ASCII codes for handling characters in a string. This enables ranges that organize cleanly with Select Statements. This is especially useful for rejecting characters from unsophisticated users like my parents (I did not name their grandson "4").

Below is a UDF that would work for the OP, but also shows how one could leverage the VBA Asc function combined with a select statement for handling, upper/lower case, or any other specific characters:

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

Answer 2

Consider the following User Defined Function:

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

All numbers will be returned as a space-separated string

Answer 3

a slight variation of Gary's Student's answer:

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

Answer 4

NDIGITS (UDF)

Excel Formula

=NDIGITS($A1,6)

Sample Data

VBA Code

'******************************************************************************
' 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.
'******************************************************************************

Answer 5

If you have Office 365 you can use this array formula:

=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),""))

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting Edit Mode.

Answer 6

If "E", "W", "F" and "S" are the only letters you must get rid of, you can avoid VBA and use SUBSTITUTE() function:

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

Answer 7

use Right() function and get 6 rightmost character. for example:

 Right(cell.Value, 6)

where cell is some Range variable addressing relevant cell

for instance

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