Extracting Numbers Greater Than 3 Characters from a Text

[Ongbey]

My goal is to find numbers larger than 3 characters in a text and copy them into consecutive cells.
I explained this in the attached file.

Not sure how to do it without using macros. If it can be handled with the formula, it is my preference.
Thanks in advance.

 

[Ongbey]

In fact, my purpose is to get only numeric part of alpha-numeric expression. For example; if there is 89567X, I need to get only 89567. If there is 66555LL, I need to get only 66555.

 

[Joe4]

If I wanted one more thing, I hope it wouldn't be too much.

Our codes are sometimes alphanumeric, but the letters are always at the end of the numeric. For example, next to 89567 and 66555 we have similar codes 89567X or 66555LL. Is it possible to get alphanumeric part codes along with these numeric codes?

That really changes the nature of the question quite significantly. My code depends on the entire "word" (string) being numeric.

I have some ideas, but it will take some time for me to play around with it. I will see if I can find some time to look at it a little later today.

 

[Joe4]

Try this code:

Function GetNum(str As String, num As Integer) As Variant
'   Get numbers in string that are at least 3 digits
'   - str: string to search
'   - num: which number to bring back (1 = first, 2 = second, ...)

    Dim arr() As String
    Dim i As Long
    Dim n As Long
    Dim ret As Double
    Dim nn As Long
    
'   Split string by spaces and store in an array
    arr = Split(str, " ")
    
'   Loop through values in array
    For i = LBound(arr) To UBound(arr)
'       Count the number of numbers at the beginning of the string
        nn = CountNums(arr(i))
'       See if three or more numbers
        If nn >= 3 Then
'           Increment counter
            n = n + 1
'           See if this is the number you are looking for
            If n = num Then
'               Record number
                ret = Left(arr(i), nn)
'               Exit for loop
                Exit For
            End If
        End If
    Next i
    
'   See if it found a value
    If ret = 0 Then
        GetNum = "-"
    Else
        GetNum = ret
    End If
        
End Function


Function CountNums(str As String) As Long
'   Counts the number of numbers at the beginning of a string/word

    Dim i As Long
    Dim j As Long
    Dim n As Long
    
'   Find length of string
    j = Len(str)

'
    If j > 0 Then
        For i = 1 To j
            If IsNumeric(Mid(str, i, 1)) Then
                n = i
            Else
                Exit For
            End If
        Next i
    End If
    
    CountNums = n
    
End Function

You should be able to use the same formula on your worksheet as I gave before. These changes just update the VBA code behind the functions.

 

[Ongbey]

Your code works great. Thank you very much

 

[Joe4]

You are welcome.