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.11874

[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.