Strip amount from a string

[jovii]

Hi all,

I have a string where I need to strip out an amount. The string
looks something like this:
"1.00 cents per gallon premium to July 2009 NYMEX RBOB Gasoline
contract. This sale is part of an EFP in accordance with the rules and
regulations of the NYMEX for such a transaction. BP NORTH AMERICA
PETROLEUM will transfer to VITOL INC a number of July 2009 RBOB
Gasoline contracts equal to the outturned volume rounded to the
nearest thousand barrels divided by 1000. Mutually agreeable price to
be set and an EFP to be effected during normal NYMEX trading hours"

I need to strip out the amount that precedes the word: cents so for
the above I need to return 1.00. The problem is cents per gallon can
appear anywhere in the string. I can use the instr function to
determine where the starting position of the word: "cents" , but don't
know how to get the amount.

Also, the cents per gallon price can be up to 7 positions. 2 numbers
left of the decimal and 4 numbers right of the decimal.


Any help is greatly appreciated.

Thank You

Joe V

 

[Bob Quintal]

m:

Hi all,

I have a string where I need to strip out an amount. The
string
looks something like this:
"1.00 cents per gallon premium to July 2009 NYMEX RBOB Gasoline
contract. This sale is part of an EFP in accordance with the rules
and regulations of the NYMEX for such a transaction. BP NORTH
AMERICA PETROLEUM will transfer to VITOL INC a number of July
2009 RBOB Gasoline contracts equal to the outturned volume rounded
to the nearest thousand barrels divided by 1000. Mutually
agreeable price to be set and an EFP to be effected during normal
NYMEX trading hours"

I need to strip out the amount that precedes the word: cents so
for the above I need to return 1.00. The problem is cents per
gallon can appear anywhere in the string. I can use the instr
function to determine where the starting position of the word:
"cents" , but don't know how to get the amount.

Also, the cents per gallon price can be up to 7 positions. 2
numbers left of the decimal and 4 numbers right of the decimal.


Any help is greatly appreciated.

Thank You

Joe V

Once you have the position of the "cents" string, use the mid()
function to grab the characters by counting down from the position
returned by your instr() function.


Public Function fnGetCents(ByVal stInput As String) As Currency
'=====================
'(c) 2009, Bob Quintal
'permission to use is granted as long
'as this Copyright notice remains.
'=====================
Dim ichar As Integer
Dim iEnd As Integer
Dim iStart As Integer
Dim stCents As String
Dim stChar As String

iEnd = InStr(stInput, "cents") - 2 ' don't need " C"
iStart = iEnd - 9
If iStart < 1 Then iStart = 1

For ichar = iEnd To iStart Step -1
stChar = Mid(stInput, ichar, 1)
If IsNumeric(stChar) Or stChar = "." Then
stCents = stChar & stCents
End If
Next
fnGetCents = val(stCents)
End Function

 

[jovii]

Thanks for the help Bob. It's jut what I'm looking for.

 

[jovii]

Thanks for all the replies. 3 very different ways to get the same
results. I never knew about the instrRev() function. That could come
in handy in the future.