Steve, you could try something like this. It's probably not the most efficient thing to do, so I'm interested to see what some of the gurus suggest.
Sub ExtractMoney()
Dim i As Long
Dim c As Variant
Dim arParts() As String
Dim rng As Range
Dim strLo As String
Dim strHigh As String
Application.ScreenUpdating = False
Set rng = Sheets("Now").Range("A5:A" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each c In rng
strLo = ""
strHigh = ""
arParts = Split(c.Offset(0, 1), " ", -1)
For i = 0 To UBound(arParts) - 1 Step 1
If Left(arParts(i), 1) = "£" Then
If strLo = "" Then
strLo = arParts(i)
Else
If strHigh = "" Then
strHigh = arParts(i)
End If
End If
End If
Next i
'Debug.Print c.Value & " " & strLo & " " & strHigh
c.Offset(0, 3) = c.Value
c.Offset(0, 4) = strLo
c.Offset(0, 5) = strHigh
Next c
Application.ScreenUpdating = True
End Sub
Greg
Bookmarks