hello
i have the following text in cell A2
123456 batman £15000.00
i would like to be able to coy just the £ amounts in to cell D2.

could anyone please help me.

Kind regards

CR

Hi,

Am Sat, 18 May 2013 14:06:42 +0100 schrieb lostgrave2001:

hello
i have the following text in cell A2
123456 batman £15000.00
i would like to be able to coy just the £ amounts in to cell D2.

in D2 try:
=MID(A2,FIND("£",A2),99)


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

Hi lostgrave2001

Try the following, this will extract the value as a number, format cell as currency.

=--TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2)))

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 "

Thank you again in advance.

CR

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

Hi lostgrave2001

Assuming that text is in A2 again.
In E2: =--TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2),LEN(A2)))
In F2: =--TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2)))