> I was wondering whether anyone could help me out splitting out the
following
string (an excel formula) using a regular expression. What I would like
to be able to do is get the dependency paths, the sheets names and the rows
and columns split into seperate variables.
"=[dependencytwo.xml]Sheet1!R1C1+[dependencytwo.xml]Sheet1!R1C2"
I suggest to use a regular expression to match each cell and retrieving
information about each match with backreferences.
VB code follows.
Imports System.Text.RegularExpressions
....
Dim Rx As RegEx = New
RegEx("(\[)?<DEP>[^])]+?\]){0,1}((?<SHEET>[A-z,0-9]+?)\!){0,1}R(?<ROW>[0-9]+
?)C(?<COL>[0-9]+?)")
Dim Cells As MatchCollection = RegEx.Matches("string to be parsed")
Dim Cell As Match
For Each Cell In Cells
' Cell.Value retrieves the entire cell reference, e.g.
[dependencytwo.xml]Sheet1!R1C5
' Cell.Groups("DEP") gets the dependency, e.g. dependencytwo.xml
'Cell.Groups("SHEET") gets the sheet name, e.g. Sheet1
'Cell.Groups("ROW") gets row number, e.g. 1
'Cell.Groups("COL") gets column number, e.g. 5
Next Cell
Remember that all tokens are returned as strings.