Excel VBA - Extract numeric value in string

Question

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!

Could there be any numbers within the strings either side of the dollar value? — Jordan
– Jordan, Commented Nov 1, 2016 at 17:43
All three suggestions worked. Thank you for your input. You saved me a ton of time. — MDMFD87
– MDMFD87, Commented Nov 1, 2016 at 20:35

Comintern · Accepted Answer · 2016-11-01 17:55:34Z

4

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

answered Nov 1, 2016 at 17:55

Comintern

22.3k6 gold badges36 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Coleman · Accepted Answer · 2016-11-01 17:55:16Z

3

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,

answered Nov 1, 2016 at 17:55

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

Comments

maxhob17 · Accepted Answer · 2016-11-01 18:12:18Z

3

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:

answered Nov 1, 2016 at 18:12

maxhob17

6223 silver badges9 bronze badges

Comments

Jordan · Accepted Answer · 2016-11-01 17:54:15Z

1

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

answered Nov 1, 2016 at 17:54

Jordan

4,5342 gold badges22 silver badges33 bronze badges

Comments

user3598756 · Accepted Answer · 2016-11-01 18:02:09Z

0

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

answered Nov 1, 2016 at 18:02

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Collectives™ on Stack Overflow

Excel VBA - Extract numeric value in string

5 Answers 5

Comments

Comments

Comments

Code

Result

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Code

Result

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related