Result of formula isn't a numer

Drclone 

It works in Microsoft 365 and Office 2021, not in older versions.

In Office 2019, you can use (confirmed with Ctrl+Shift+Enter):

=--TEKST.SAMENV(ALS(ISGETAL(--DEEL(A1,RIJ(INDIRECT("1:"&LENGTE(A1))),1)),DEEL(A1,REEKS(LENGTE(A1)),1),""))

In older versions, you can create a custom VBA function in the Visual Basic Editor:

Function ExtractNumber(s As String) As Long
    Dim v As String
    Dim i As Long
    For i = 1 To Len(s)
        If IsNumeric(Mid(s, i, 1)) Then
            v = v & Mid(s, i, 1)
        End If
    Next i
    If v <> "" Then
        ExtractNumber = Val(v)
    End If
End Function

Use like this:

=ExtractNumber(A1)

You'll have to save the workbook as a macro-enabled workbook (.xlsm) and allow macros when you open it.