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]

Below I put the text if you need

Expalanation text	Number1	Number2	Number3	Number4	Number5	Number6
Today, 15 pcs of part code 89567 were shipped to customer 98745. And part code 66555 products were located to the warehouse.	89567	98745	66555	-	-	-

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Joe4]

I came up with a User Defined Function to do this.
Just place this code in a new VBA module in your workbook:

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
    
'   Split string by spaces and store in an array
    arr = Split(str, " ")
    
'   Loop through values in array
    For i = LBound(arr) To UBound(arr)
        If IsNumeric(arr(i)) Then
'           If a number with at least 3 digits
            If arr(i) >= 100 Then
'               Increment counter
                n = n + 1
'               See if this is the number you are looking for
                If n = num Then
'                   Record number
                    ret = arr(i)
'                   Exit for loop
                    Exit For
                End If
            End If
        End If
    Next i
    
'   See if it found a value
    If ret = 0 Then
        GetNum = "-"
    Else
        GetNum = ret
    End If
        
End Function

Then, enter this formula in cell B2 and copy across:

=GetNum($A2,COLUMN()-1)

BTW, I am curious to see if Fluff can come up with a non-VBA formula how to do it.
I would love to see if it could be done without VBA (I imagine it is possible, but may be beyond my formula capabilities!).

 

[Sufiyan97]

If you are using 365

Try

Book1
	A	B	C	D	E	F	G	H
1	Expalanation text	Number1	Number2	Number3	Number4	Number5	Number6
2	Today, 15 pcs of part code 89567 were shipped to customer 98745. And part code 66555 products were located to the warehouse.	89567	98745	66555
3
4
Sheet8

Cell Formulas
Range	Formula
B2:D2	B2	=LET(a,IFERROR(IF(LEN(TEXTSPLIT(A2," ")+0)>3,TEXTSPLIT(A2," ")+0,""),""),FILTER(a,a<>""))
Dynamic array formulas.

 

[Fluff]

3 options depending on the OP's version

Fluff.xlsm
	A	B	C	D	E	F
1
2	Today, 15 pcs of part code 89567 were shipped to customer 98745. And part code 66555 products were located to the warehouse.	89567	98745	66555		365 or 2021
3		89567	98745	66555		365
4		89567	98745	66555		2013 or newer
Lists

Cell Formulas
Range	Formula
B2:D2	B2	=TRANSPOSE(FILTERXML("<k><m>"&SUBSTITUTE(A2," ","</m><m>")&"</m></k>","//m[.=number() and string-length()>3]"))
B3:D3	B3	=LET(s,TEXTSPLIT(A2,{" ","."},,1),FILTER(--s,(ISNUMBER(--s))*(LEN(s)>3)))
B4:E4	B4	=IFERROR(FILTERXML("<k><m>"&SUBSTITUTE($A2," ","</m><m>")&"</m></k>","//m[.=number() and string-length()>3]["&COLUMNS($B4:B4)&"]"),"")
Dynamic array formulas.

 

[Joe4]

sufiyan97 and fluff,

Pretty cool formula options!
I keep forgetting about the new TEXTSPLIT function. I love that!

 

[Fluff]

Yup, Textsplit is very useful

 

[Ongbey]

Sorry for lack of information. I completed my profile as Excel 2013.
Thanks your efforts. However, I think LET and FILTERXML function is not exist in my Excel version. So, I will try the VBA version of the solution.

 

[Ongbey]

' - str: string to search

Joe4 your Getnum function works great

I was not expecting this.

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?