Extract numbers from text string

[Ringhal]

In the attached workbook, I have something that looks like this:
xxx 3.76 ZAR xxx 0.926 ZAR xxx
xxx 3.76 ZAR xxx 0.39 ZAR xxx
xxx 3.76 ZAR

The above is repeated over a number of rows, where the text and numbers are random, but follow the same pattern as above, as seen in the attached file. The ZAR is always after a number.

I found a UDF that extracts numbers from text strings. http://www.ozgrid.com/VBA/ExtractNum.htm , but it combines the two separate numbers into one cell. I need the numbers separated and in adjacent cells.

An idea I had for solving this is, find the ZAR in the line and extract the number to the left of it.

In assistance is greatly appreciated.Sample Extract Numbers.xlsx

[jonh]

Sub x()
    If Selection.Columns.Count <> 1 Then
        MsgBox "too many columns selected"
        Exit sub
    End if
    For Each c In Selection.Cells
        a = Split(c.Text, Chr(32))
        For Each s In a
            If IsNumeric(s) Then
                i = i + 1
                Cells(c.Row, c.Column + i) = s
            End If
        Next
        i = 0
    Next
End Sub

[Ringhal]

Thanks jonh, it works perfectly.

[Bob Phillips]

Public Function ExtractNumbers(ByRef rng As Range) As Variant
Dim numCells As Long
Dim ary As Variant
Dim aryidx As Long
Dim i As Long, ii As Long

    numCells = Application.Caller.Cells.Count
    
    ReDim ary(1 To numCells)
    i = 1
    Do Until i > Len(rng.Value)
    
        Do Until IsNumeric(Mid(rng.Value, i, 1)) Or i > Len(rng.Value)
        
            i = i + 1
        Loop
        
        If i <= Len(rng.Value) Then
        
            aryidx = aryidx + 1
            
            ii = i + 1
            Do Until (Not IsNumeric(Mid(rng.Value, ii, 1)) And Mid(rng.Value, ii, 1) <> ".") Or i >= Len(rng.Value)
            
                ii = ii + 1
            Loop
            
            ary(aryidx) = Val(Mid(rng.Value, i, ii - i))
            
            i = ii
        End If
    Loop
    
    For i = 1 To numCells
    
        If IsEmpty(ary(i)) Then ary(i) = ""
    Next i
    
    ExtractNumbers = ary
End Function

You need to select a number of cells alongside your string, 4, 5 or whatever, then enter the formula =ExtractNumbers(C1) as an example, as a block-array formula, that is Ctrl-Shift-Enter.

[snb]

Sub M_snb()
    sn = Cells(1, 3).CurrentRegion

    For j = 1 To UBound(sn)
        Cells(j, 5).Resize(, UBound(Filter(Split(sn(j, 1)), ".")) + 1) = Filter(Split(sn(j, 1)), ".")
    Next
End Sub

[Ringhal]

Thank you xld and snb for your contributions.