Copying out £ amount only

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

could anyone please help me.

Kind regards

C

 

[Claus Busch]

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

 

[Kevin@Radstock]

Hi lostgrave2001

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

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

 

[lostgrave2001]

Thank you both for your responses they both worked great. i know have
similar problem once cell has two amounts in the same cell is there an
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

 

[Claus Busch]

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

 

[Kevin@Radstock]

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)))