I have text which is imported into a spreadsheet.

All the text is in an individual cell but within that text will be a value
that i want to capture.

for examle

"the selling price for this is £2.99 and the product code is 123456"

i have similar text going down in column A for about 120 rows?

the value i want is the price being £2.99, i can't use the left, right or
mid function as the length of the text can be any length.

Is there another function i can use that can pick up this value?

i'm trying to write a macro that will pick up this value and use it for
further calulations.

Next question

If i want a macro to stop running and close excel completely without saving
the spreadsheet what is the code for this?

for example

if activecell.value = "" then
close excel??

thank you in advance

hervinder

1. "the selling price for this is £2.99 and the product code is 123456"

=LEFT(MID(A2,SEARCH("L",A2)+1,256),SEARCH(" ",MID(A2,SEARCH("L",A2)+1,256))-1)


2. if activecell.value = "" then
ThisWorkbook.Saved = True
Application.Quit
End if

Please, next time post different questions in separate messages!

Regards,
Stefi

1. This will return the amounts from the sentence:

Function GetAmounts(ByVal Sentence As String) As Variant
Digits = "0123456789 " & "." ' Application.DecimalSeparator
Sentence = Replace(Sentence, ",", "") ' Thousand separators
For i = 1 To Len(Sentence)
Select Case InStr(Digits, Mid(Sentence, i, 1))
Case Is = 0
Mid(Sentence, i, 1) = " "
End Select
Next
GetAmounts = Trim(Sentence)
Do Until 0 = InStr(GetAmounts, " ")
GetAmounts = Replace(GetAmounts, " ", " ")
Loop
GetAmounts = Split(GetAmounts, " ")
End Function

Sub xx()
xy = GetAmounts("the selling price for this is £2.99 and the product
code is 123456")
End Sub

?xy(0)
2.99
?xy(1)
123456
?ubound(xy) ' Add 1 to this- split use Option Base 0 regardless of your
setting
1

2. ActiveWorkbook.Saved = True
Application.Quit

"Hervinder" wrote:

I have text which is imported into a spreadsheet.

All the text is in an individual cell but within that text will be a value
that i want to capture.

for examle

"the selling price for this is £2.99 and the product code is 123456"

i have similar text going down in column A for about 120 rows?

the value i want is the price being £2.99, i can't use the left, right or
mid function as the length of the text can be any length.

Is there another function i can use that can pick up this value?

i'm trying to write a macro that will pick up this value and use it for
further calulations.

Next question

If i want a macro to stop running and close excel completely without saving
the spreadsheet what is the code for this?

for example

if activecell.value = "" then
close excel??

thank you in advance

hervinder

Copying changed £ the pound sign to L, the correct formula is:

=LEFT(MID(A2,SEARCH("£ ",A2)+1,256),SEARCH(" ",MID(A2,SEARCH("£
",A2)+1,256))-1)


Stefi