CODEWITHSUNDEEP
excelvbaextract
MDMFD87
MDMFD87

Reputation: 31

Excel VBA - Extract numeric value in string

I have multiple tables of data in which, in one column, I have information about the value of various contracts. For each contract, the information contained in each cell of that column is presented as follows:

"The value of the contract is $XX,XXX.XX."

Sometimes, there will be additional text after the dollar value, as follows:

"The value of the contract is $XX,XXX.XX. There is an option to modify the duration of the contract"

I need to program a sub that allows me to extract the dollar value within that string of text and only keep that information (and none of the text coming before and after).

The difficulty I'm facing here is that everything is subject to change. The dollar value is never the same and the text before or after it also changes.

So far I've been able to successfully keep everything after the $ sign, using the SPLIT function and $ as delimiter. However, I keep having problems removing whatever text may follow the dollar value.

Any idea on how I could proceed?

Thank you for your help!

Upvotes: 3

Views: 14925

Answers (5)

maxhob17
maxhob17

Reputation: 612

Just in case its easier - you might not actually need a sub. A formula such as this:

=VALUE(LEFT(MID(B3,FIND("$",B3)+1,LEN(B3)),FIND(".",B3)-FIND("$",B3)+2))

works for this example:

enter image description here

Upvotes: 3

user3598756
user3598756

Reputation: 29421

if you don't bother last period

Function GetMoney(txt As String) As String
    GetMoney = "$" & Split(Split(txt, "$")(1), " ")(0)
End Function

else

Function GetMoney(txt As String) As String
    GetMoney = "$" & Split(Split(txt, "$")(1), " ")(0)
    GetMoney = Left(GetMoney, Len(GetMoney) - 1)
End Function

Upvotes: 0

Comintern
Comintern

Reputation: 22185

The easiest way to do this is with a regular expression:

'Requires a reference to Microsoft VBScript Regular Expressions X.X
Public Function ExtractNumber(inValue As String) As Double
    With New RegExp
        .Pattern = "(\d{1,3},?)+(\.\d{2})?"
        .Global = True
        If .Test(inValue) Then
            ExtractNumber = CDbl(.Execute(inValue)(0))
        End If
    End With
End Function

Sample usage:

Sub Example()
    Debug.Print ExtractNumber("The value of the contract is $12,345.67. More text.")
End Sub

Upvotes: 4

John Coleman
John Coleman

Reputation: 51988

The VBA function val() has the nice property that it isn't bothered by text which comes after the number. So something like the following is possible:

Function ExtractAmount(data As String) As Variant
    Dim s As String
    s = Split(data, "$")(1) 'part after first dollar sign
    s = Replace(s, ",", "") 'throw away any commas
    ExtractAmount = CCur(Val(s))
End Function

For example,

enter image description here

Upvotes: 3

Jordan
Jordan

Reputation: 4514

Provided there are no numbers within the string other than the dollar value, this will work:

Code

Sub testingsub()

Dim str As String
Dim x, p1, p2 As Integer

str = "The value of the contract is $00,000.00. There is an option to modify the duration of the contract"
p1 = InStr(str, "$")

For x = Len(str) To 1 Step -1
    If IsNumeric(Mid(str, x, 1)) Then
        p2 = x + 1
        Exit For
    End If
Next x

Debug.Print Mid(str, p1, p2 - p1)

End Sub

Result

$00,000.00

Upvotes: 1

Related Questions