GetNumeric Function issue

[Trying2learnVBA]

Hello,

I would like to extract the number after USD in this string:

03-Oct-2022 Swap Net Coupon CME IRS Trade: 15360193 03-Oct-2022 USD 127,575.00 DB

I've tried different methods, formulas, some vba methods.

this is my current code and I can't get it to work. I get an error. It says variable not defined and the 'i' gets highlighted. Any ideas as to what i am doing wrong?

Function GetNumeric(CellRef As String)
Dim StringLength As Integer
StringLength = Len(CellRef)
For i = 1 To StringLength
If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1)
Next i
GetNumeric = Result
End Function

 

[JoeMo]

How about:

Book1
	A	B
1	03-Oct-2022 Swap Net Coupon CME IRS Trade: 15360193 03-Oct-2022 USD 127,575.00 DB	127575
2
3	03-Oct-2022 Swap Net Coupon CME IRS Trade: 15360193 03-Oct-2022 USD 127,575.00	127575
Sheet3

Cell Formulas
Range	Formula
B1:B3	B1	=ExtractUSDAmount(A1)

Function ExtractUSDAmount(S As String) As Variant
Dim V As Variant
If S = "" Or InStr(S, "USD") = 0 Then
    ExtractUSDAmount = ""
    Exit Function
End If
V = Split(S, " ")
For i = LBound(V) To UBound(V) - 1
    If V(i) = "USD" Then
        If IsNumeric(V(i + 1)) Then
            ExtractUSDAmount = Val(Replace(V(i + 1), ",", ""))
            Exit For
        End If
    End If
Next i

End Function

Format cells in col B to suit (for example as currency).

 

[Peter_SSs]

I've tried different methods, formulas

We might need more than one example to make the formula(s) robust, but what about these for a start?
Format the result cell with whatever format you want.

Trying2learnVBA.xlsm
	A	B	C
1	03-Oct-2022 Swap Net Coupon CME IRS Trade: 15360193 03-Oct-2022 USD 127,575.00 DB	127575	127575
Sheet1

Cell Formulas
Range	Formula
B1	B1	=TEXTBEFORE(TEXTAFTER(A1," USD ")," ")+0
C1	C1	=LET(f,FIND(" USD ",A1),REPLACE(LEFT(A1,FIND(" ",A1,f+6)),1,f+3,"")+0)

 

[Trying2learnVBA]

We might need more than one example to make the formula(s) robust, but what about these for a start?
Format the result cell with whatever format you want.

Trying2learnVBA.xlsm
	A	B	C
1	03-Oct-2022 Swap Net Coupon CME IRS Trade: 15360193 03-Oct-2022 USD 127,575.00 DB	127575	127575
Sheet1

Cell Formulas
Range	Formula
B1	B1	=TEXTBEFORE(TEXTAFTER(A1," USD ")," ")+0
C1	C1	=LET(f,FIND(" USD ",A1),REPLACE(LEFT(A1,FIND(" ",A1,f+6)),1,f+3,"")+0)

Hey thank you for this - C1 formula works great. B1 formula does not work for me. I get #NAME something with my syntax. But C1 is a fine solution. Thank you again

 

[Trying2learnVBA]

How about:

Book1
	A	B
1	03-Oct-2022 Swap Net Coupon CME IRS Trade: 15360193 03-Oct-2022 USD 127,575.00 DB	127575
2
3	03-Oct-2022 Swap Net Coupon CME IRS Trade: 15360193 03-Oct-2022 USD 127,575.00	127575
Sheet3

Cell Formulas
Range	Formula
B1:B3	B1	=ExtractUSDAmount(A1)

Function ExtractUSDAmount(S As String) As Variant
Dim V As Variant
If S = "" Or InStr(S, "USD") = 0 Then
    ExtractUSDAmount = ""
    Exit Function
End If
V = Split(S, " ")
For i = LBound(V) To UBound(V) - 1
    If V(i) = "USD" Then
        If IsNumeric(V(i + 1)) Then
            ExtractUSDAmount = Val(Replace(V(i + 1), ",", ""))
            Exit For
        End If
    End If
Next i

End Function

Format cells in col B to suit (for example as currency).

Hey this seems to work perfectly for you. However, i am getting the same error I get with the code I posted originally.
"Variable not defined" and the "i" is highlighted. I do not see a dim for "i" do you have this variable defined in another workbook? how should I define "i"?

 

[JoeMo]

Hey this seems to work perfectly for you. However, i am getting the same error I get with the code I posted originally.
"Variable not defined" and the "i" is highlighted. I do not see a dim for "i" do you have this variable defined in another workbook? how should I define "i"?

Try adding: Dim i as Long after the Dim V as Variant line.

 

[Peter_SSs]

B1 formula does not work for me. I get #NAME

Sounds like you just don't have the TEXTBEFORE and TEXTAFTER functions yet. You should get them in due course.

For another user-defined function, based on your one data sample, you could also try this one.
It includes an option argument if you wanted to find currencies other than USD - eg Australian dollars in row 4 below

Function CurrencyAmount(s As String, Optional CurrencyCode As String = "USD")
  CurrencyAmount = Val(Split(Replace(s, ",", "") & " " & CurrencyCode & " 0", " " & CurrencyCode & " ")(1))
End Function

Trying2learnVBA.xlsm
	A	B
1	03-Oct-2022 Swap Net Coupon CME IRS Trade: 15360193 03-Oct-2022 USD 127,575.00 DB	127575
2	03-Oct-2022 Swap Net Coupon CME IRS Trade: 15360193 03-Oct-2022 GBP 127,575.00 DB	0
3
4	03-Oct-2022 Swap Net Coupon CME IRS Trade: 15360193 03-Oct-2022 AUD 68,234.99 DB	68234.99
Sheet3

Cell Formulas
Range	Formula
B1:B2	B1	=CurrencyAmount(A1)
B4	B4	=CurrencyAmount(A4,"AUD")