Only Extract number from text string which is surrounded by spaces

[bzuko22]

Hi all,

I know there few posts about extracting numbers from text strings which I'm OK to do. My issue is to extract ONLY a number which is in between spaces, for example:

Source data - cell A	Result - cell B
24m swimming 36 george123	36
48 challenge23 yellow	48
11xyz 12 run michael	12
tennis nadal george123 brown 56	56

<tbody>
</tbody>

Is this something simple to do, ideally i'd like to have macro tu run to produce this, since dealing with lots of data.

Many thanks

 

[Joe4]

Welcome to the Board!

Here is a User Defined Function I created in VBA. You can use this like any other function right on the spreadsheet, or called from other VBA code:

Function FindNumber(myEntry As Variant) As Double
'   Pulls out the first solitary number it finds in a sentence


    Dim Words() As String
    Dim i As Long


'   Split string into words, using space as delimiter
    Words = Split(myEntry, " ")


'   Loop through all the words, looking for numeric only entries
    For i = LBound(Words) To UBound(Words)
        If IsNumeric(Words(i)) Then
            FindNumber = Words(i)
            Exit For
        End If
    Next i


End Function

So, if you wanted to use it on the spreadsheet, and wanted to extract the value from cell A1, just use this formula:
=FindNumber(A1)

 

[bzuko22]

Thank you for this - you are a star!

 

[István Hirsch]

This formula works with the sample given:

=LOOKUP(10^9,1*TRIM(MID(SUBSTITUTE(" "&A1&" "," ",REPT(" ",99)),99*ROW($1:$100),99)))

<tbody>
</tbody><colgroup><col></colgroup>