Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 10
Question Copying out £ amount only

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
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,872
Default Copying out £ amount only

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
  #3   Report Post  
Member
 
Posts: 93
Default

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

Quote:
Originally Posted by lostgrave2001 View Post
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
  #4   Report Post  
Junior Member
 
Posts: 10
Question

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



Quote:
Originally Posted by lostgrave2001 View Post
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
  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,872
Default Copying out £ amount only

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


  #6   Report Post  
Member
 
Posts: 93
Default

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

Quote:
Originally Posted by lostgrave2001 View Post
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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculate the amount of time over a permitted amount (12 hours) Steve M Excel Discussion (Misc queries) 9 June 2nd 10 09:25 PM
Printing a certain amount of pages based on amount of data guerilla Excel Programming 0 August 18th 07 08:01 AM
Copying multiple rows to other worksheets (but amount of rows varies) - How? David Smithz Excel Discussion (Misc queries) 1 June 18th 06 04:31 PM
Formula for amount owing subtract amount paid Taperchart Excel Worksheet Functions 1 June 4th 06 05:51 PM
How can I calculate amount of time left based on amount spent? KLD Excel Worksheet Functions 3 May 23rd 06 04:20 PM


All times are GMT +1. The time now is 10:09 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"