Hi CR,

Am Sun, 19 May 2013 23:00:20 +0100 schrieb lostgrave2001:

Thank you both for your responses they both worked great. i know have a
similar problem once cell has two amounts in the same cell is there any
way i can put these in two to separate cells e2 and f2?
"betta £1200.00 40 /1234564 gamma £3000.00 "

try it with a macro:

Sub SeparateAmounts()
Dim LRow As Long
Dim rngC As Range
Dim Start1 As Integer
Dim Start2 As Integer
Dim End1 As Integer
Dim myStr1 As String
Dim myStr2 As String

LRow = Cells(Rows.Count, 1).End(xlUp).Row
For Each rngC In Range("A2:A" & LRow)
myStr1 = ""
myStr2 = ""
Start1 = InStr(rngC, "£")
Start2 = InStrRev(rngC, "£")
If Start2 = Start1 Then
myStr1 = Trim(Mid(rngC, InStr(rngC, "£") + 1, 99))
Else
myStr2 = Trim(Mid(rngC, Start2 + 1, 99))
Start1 = InStr(rngC, "£")
End1 = InStr(Start1, rngC, " ")
myStr1 = Mid(rngC, Start1 + 1, End1 - Start1)
End If
rngC.Offset(0, 3) = myStr1
rngC.Offset(0, 4) = myStr2
Range("D2:E" & LRow).NumberFormat = "[$£-809]#,##0.00"
Next
End Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2