Return only first numeric digits in a seperate column

Hi,

first use the following formula to get to know the first occurrence of number

=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))

Select allOpen in new window

then use the following code to get the numbers in another column

=RIGHT(A1,LEN(A1)-position+1)

Select allOpen in new window

You could create a Pivot Table of unique items from the data and then select only those values that you want to include. However, this would show the whole value rather than just the first two digits.

How about some tiny VBA code ?

Public Function FirstDigit(str As String) As Variant

    Dim c As String
    Dim i As Integer
    
    For i = 1 To Len(str)
        c = Mid(str, i, 1)
        If InStr(1, "0123456789", c) > 0 Then
            FirstDigit = c
            Exit Function
        End If
    Next
    
    ' or something else as desired if there's no digit in str
    FirstDigit = CVErr(xlErrNA)

End Function

Select allOpen in new window

See example workbook attached ...
example.xlsm

@Subodh Tiwari (Neeraj) - when I click within the cell with the formula and click enter the result changes from the 'expected result' to the first two digits of the dataset (both alpha numeric and numeric).  Can you please check this?

As I said, the suggested formula is an Array Formula which requires confirmation with Ctrl+Shift+Enter.
If a formula is entered as an array formula, you will notice that in the formula bar, the formula gets surrounded by the curly braces.
And if you don't see the curly braces in the formula bar that means the array formula hasn't been entered correctly and in that case select the formula cell --> press F2 to go in edit mode --> now hold down the Ctrl+Shift keys together and hit Enter.

I've misread the question a bit, and now I've made up the VBA code.

Public Function FirstDigits(str As String) As Variant

    Dim c As String
    Dim i As Integer
    
    For i = 1 To Len(str)
        c = Mid(str, i, 1)
        If InStr(1, "0123456789", c) > 0 Then
            FirstDigits = c
            If i < Len(str) Then
                For j = i + 1 To Len(str)
                    c = Mid(str, j, 1)
                    If InStr(1, "0123456789", c) > 0 Then
                      FirstDigits = FirstDigits + c
                    Else
                      Exit Function
                    End If
                Next
            End If
            Exit Function
        End If
    Next
    
    'rem or something else
    FirstDigits = CVErr(xlErrNA)

End Function

Select allOpen in new window

The function returns the first uninterrupted group of digits in the string. If there's no digit in the strin, it returns #NA.

That's the result for some test strings:

example.xlsm

Based on a previous question from Wonderwall, summing a dataset on the basis of the first two characters of a string, I believe Neeraj has the correct interpretation of the question; the requirement is for the first two characters of the string, if they are numeric then use otherwise ignore.

Last correction:

Public Function FirstDigits(str As String) As Variant

    Dim c As String
    Dim i As Integer
    
    For i = 1 To Len(str)
        c = Mid(str, i, 1)
        If InStr(1, "0123456789", c) > 0 Then
            FirstDigits = c
            If i < Len(str) Then
                For j = i + 1 To Len(str)
                    c = Mid(str, j, 1)
                    If InStr(1, "0123456789", c) > 0 Then
                      FirstDigits = FirstDigits + c
                      If Len(FirstDigits) = 2 then Exit Function
                    Else
                      Exit Function
                    End If
                Next
            End If
            Exit Function
        End If
    Next
    
    'rem or something else
    FirstDigits = CVErr(xlErrNA)

End Function

Select allOpen in new window

(not incorporated in example).

Returns 2 digits at most, starting from what position ever.

Thanks for all your help!  I was able to get the Array formula to work.  I selected the 'Array Formula' solution because I'm not familiar with VBA and would be unable to update in the future if we needed a change.

You're welcome. Glad it worked.