Only first numbers extract

[sohaila]

Hi,

can any one help me i want to extract only first numbers in string like
"new trick#14- fhf 34" to extract only 14 and
"dfr new trick 1 fdff 7 df 787" to 1
"dff -trt# 77 df 4 " to 77

Is this possible?

thanks

[Phuocam]

Try this:

=LOOKUP(10^17,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"1234567890")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16}))

[mikerickson]

You could use a UDF like this

Function ExtractValue(FromString As String, Optional ExtractIndex As Long = 1) As Double
    Dim i As Long, tVal As Double
    
    For i = 1 To Len(FromString)
        If Mid(FromString, i) Like "#*" Then
            ExtractIndex = ExtractIndex - 1
            tVal = Val(Mid(FromString, i))
            FromString = Replace(FromString, tVal, vbNullString, Count:=1)
        End If
        If ExtractIndex = 0 Then
            ExtractValue = tVal
            Exit For
        End If
    Next i
End Function

[David A Coop]

I found this on another forum.

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),2)+0 - this will work provided the first number found is 1 or 2 digits.

I hope this helps, please let me know!

Regards,

David

If this has been helpful
- Please click on the *Add Reputation button at the bottom left.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[Phuocam]

UDF

Public Function FistNum(s As String) As Long

On Error Resume Next
With CreateObject("vbscript.regexp")
.Pattern = "\d+"
FistNum = .Execute(s).Item(0)
End With
End Function

[Phuocam]

Or try:

Public Function FistNum(s As String) As Double
    Dim i As Long
    For i = 1 To Len(s)
        If IsNumeric(Mid(s, i, 1)) Then
            FistNum = Val(Mid(s, i))
            Exit For
        End If
        Next i
End Function