How to extract specific numbers from a text string in Excel?

Question

I have a data set that looks like this

abcdefg-1004958-2019-services

xyz-3104568-technology ltd-UG32594

xxfgdrtg-GH267384-FO(1082564)-2016-software

FO501117898-ahdndje-2016-service

I need to extract 1004958, 3104568, 1082564, 501117898 from this dataset. In other words, anything greater than 100000 must be returned.

Is there any formula to do this? Because I have more than 10000 entries like this, which are in uneven order, and I cannot use text to columns as well.

Any help will be greatly appreciated. Thank you!

Answer 1

Using Microsoft365, you could try:

Formula in B1:

=MAX(FILTERXML("<t><s>"&CONCAT(IFERROR(MID(A1,SEQUENCE(LEN(A1)),1)*1,"</s><s>"))&"</s></t>","//s[.*0=0]"))

Answer 2

You can use a user defined function. Here's what i came up to:

Function FunNumber(RngTarget As Range, Optional BlnReportAsNumber As Boolean = True, Optional BytPlacement As Byte = 1)
    
    'Declarations.
    Dim StrValue As String
    Dim BytPosition As Byte
    Dim StrString01 As String
    
    'Setting.
    StrValue = RngTarget.Cells(1, 1).Value
    
    'Covering each character in StrValue.
    For BytPosition = 1 To Len(StrValue)
        'Removing any non-numerica character from StrValue.
        If Not IsNumeric(Mid(StrValue, BytPosition, 1)) Then
            StrValue = Left(StrValue, BytPosition - 1) & " " & Right(StrValue, Len(StrValue) - BytPosition)
        End If
    Next
    
    'Removing leading and trailing spaces from StrValue.
    StrValue = Trim(StrValue)
    
    'Covering each character in StrValue but the last one.
    For BytPosition = 1 To Len(StrValue) - 1
        'Removing any multiple space.
        If Mid(StrValue, BytPosition, 2) = "  " Then
            StrValue = Left(StrValue, BytPosition - 1) & " " & Right(StrValue, Len(StrValue) - BytPosition - 1)
            BytPosition = BytPosition - 1
        End If
    Next
    
    'Setting FunNumber.
    FunNumber = ""
    
CP_Small_Number_Removed:

    'Covering each section of StrValue marked by a space.
    For BytPosition = 0 To UBound(Split(StrValue, " "))
        
        'Setting StrString01 as section of StrValue marked by space.
        StrString01 = Split(StrValue, " ")(BytPosition)
        
        'Checking for a number smaller than 100000.
        If Len(StrString01) < 6 Then
            
            'Removing the number smaller than 100000.
            Select Case BytPosition
                Case Is = 0
                    StrValue = Right(StrValue, Len(StrValue) - Len(StrString01) - 1)
                    GoTo CP_Small_Number_Removed
                Case Is < UBound(Split(StrValue, " "))
                    StrValue = Replace(StrValue, " " & StrString01 & " ", " ")
                    GoTo CP_Small_Number_Removed
                Case Is = UBound(Split(StrValue, " "))
                    StrValue = Left(StrValue, Len(StrValue) - Len(StrString01) - 1)
                    GoTo CP_Small_Number_Removed
            End Select
            
        End If
    Next
    
    
    'Setting FunNumber.
    FunNumber = Split(StrValue, " ")(BytPlacement - 1)
    
    'Resetting FunNumber as number if needed.
    If BlnReportAsNumber = True Then
        FunNumber = FunNumber * 1
    End If
    
End Function

Place it in a module. Let's say you string is in cell A1. In another cell write =FunNumber(A1) and you should obtain the desired result. If you want it reported as text (maybe leading zeroes are important) you can write =FunNumber(A1,FALSE). If there are more than one number greater than 100000 and you want to find for example the second, write something like =FunNumber(A1,,2) (you can of course specify if you want a string or a number while also specifying that you want the second or third or whatever number) .If no number greater than 100000 is found then the function returns #VALUE error.

Answer 3

For those who can use LibreOffice Calc, the answer is much simpler since Calc supports Regular Expressions by default:

=REGEX(A1;"\d{6,}")

The question was about Excel, but since the difference in complexity of answers is so gigantic I didn't want to withhold the LibreOffice answer.