Using regex to parse Excel range definitions

See more:

regular-expression

The ultimate goal is to have an auditing tool that will read the Defined Names portion of an Excel file through Open XML, and interpret the definition of the range names. Some ranges contain a single cell, some a rectangular range, and some are rather complex. Here's the defined names portion of a file with a variety of definitions:

<x:definedNames xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <x:definedName name="bigRange">'Sht2 Test'!$C$3:$E$8</x:definedName>
  <x:definedName name="cellabove">Sheet1!A1048576</x:definedName>
  <x:definedName name="multirange">'Sht2 Test'!$B$16:$C$18,'Sht2 Test'!$E$14:$F$16,'Sht2 Test'!$H$12:$J$15</x:definedName>
  <x:definedName name="TestRange">2*TestRange1</x:definedName>
  <x:definedName name="TestRange1">Sheet1!$C$3</x:definedName>
  <x:definedName name="ThreeD">Sheet1:Sheet3!$A$16</x:definedName>
</x:definedNames>

Using Expresso I've been able to construct the following pattern:

(?<sheet>\'?.*\'?|.*)?\!\$?(?<col>[a-zA-Z]+)\$?(?<rownum>\d+)|\$?(?<col>[a-zA-Z]+)\$?(?<rownum>\d+) 

It will capture the sheet name, the column letter, and the row number, and it works on these simple variations:

C66<br />
$AB$15<br />
'Sht2 Test'!$C$3<br />
Sheet2!$CC$44<br />

What I'm unable to figure out, and what would be nice to have, is a pattern that would work on Sheet1!$A$1:$C$4 to give me 2 separate captures, one for cell A1 and one for cell C4, and both with same sheet name

Is there a solution that doesn't involve a huge, messy pattern? If so, what might that pattern be?

Thanks,
Duke