Regex Excel Mid Issue

Question

I am essentially trying to extract the dollar amount using regex, but can't figure out how to extract just the dollar amount, which can vary in digits. Below is an example of the amount field I want to extract which is always in the middle of the field:

&ltfield1&gt05/14/2013&lt/field1&gt&ltamount&gt3,100,000.00&lt/amount&gt&ltfield3&gt026002561&lt/field3&gt

what i currently have: <amount>.*</amount> (this result doesnt get me what I want)

for this field, I want to extract just the 3.1 million figure. The structure (similar to html) surrounding the dollar figure will always be the same. Any help is appreciated.

Answer 1

Excel

Since you're doing this in excel, you might want to consider using this formula

=MID(B1,SEARCH("<amount>",B1)+8,SEARCH("</amount>",B1)-(SEARCH("<amount>",B1) + 8))

• B1 = input string
• the +8 compensates for the width of the string <amount>
• column C reveals the formula used

Regex

If you're doing this with VBA and a regex you could use the regex: <(amount)\b[^>]*>([^<]*)<\/\1>

This VB.net example is only included to show how the regex populates the group 3 with each of the dollar values found in amount tags.

Imports System.Text.RegularExpressions
Module Module1
  Sub Main()
    Dim sourcestring as String = "<field1>05/14/2013</field1><amount>3,100,000.00</amount><field3>026002561</field3>
    <field1>05/14/2013</field1><amount>4,444,444.00</amount><field3>026002561</field3>"
    Dim re As Regex = New Regex("<(amount)\b[^>]*>([^<]*)<\/\1>",RegexOptions.IgnoreCase OR RegexOptions.Multiline OR RegexOptions.Singleline)
    Dim mc as MatchCollection = re.Matches(sourcestring)
    Dim mIdx as Integer = 0
    For each m as Match in mc
      For groupIdx As Integer = 0 To m.Groups.Count - 1
        Console.WriteLine("[{0}][{1}] = {2}", mIdx, re.GetGroupNames(groupIdx), m.Groups(groupIdx).Value)
      Next
      mIdx=mIdx+1
    Next
  End Sub
End Module

$matches Array:
(
    [0] => Array
        (
            [0] => <amount>3,100,000.00</amount>
            [1] => <amount>4,444,444.00</amount>
        )

    [1] => Array
        (
            [0] => amount
            [1] => amount
        )

    [2] => Array
        (
            [0] => 3,100,000.00
            [1] => 4,444,444.00
        )

)

Answer 2

Use Excel VBA to extract your capturing group.

VBA code

Function TestRegExp(ByVal myString As String, _
                      ByVal myPattern As String, _
                      Optional seperator As String = "") As String
   Dim objRegExp As RegExp
   Dim colMatches As MatchCollection
   Dim RetStr As String

   Set objRegExp = New RegExp
   objRegExp.Pattern = myPattern
   objRegExp.IgnoreCase = True
   objRegExp.Global = True
   seperator = "|"

   If (objRegExp.Test(myString) = True) Then
    Set colMatches = objRegExp.Execute(myString)
    For i = 0 To colMatches.Count - 1
        For j = 0 To colMatches.Item(i).SubMatches.Count - 1
            If (RetStr <> "") Then
                RetStr = RetStr & seperator & colMatches.Item(i).SubMatches.Item(j)
            Else
                RetStr = colMatches.Item(i).SubMatches.Item(j)
            End If
        Next
    Next
   Else
    RetStr = "No Match"
   End If
   TestRegExp = RetStr
End Function

Excel
And the function in Excel to test this would be:

=TestRegExp(B2,"<amount>([^<]*)<\/amount>")

where cell B2 has your text:

<field1>05/14/2013</field1><amount>3,100,000.00</amount><field3>026002561</field3>
Output: 3,100,000

OR

<field1>05/14/2013</field1><amount>3,100,000.00</amount><field3>026002561</field3><amount>999</amount>  
Output: 3,100,000|999

Please note:

1. Use .*? instead of .*. This helps tackle multiple amount tags issue as it parses lazily due to the question mark. You can choose your separator in the code.
2. The trick is to use submatches to get capturing groups