regular expresdions help required

  • Thread starter Thread starter jnc
  • Start date Start date
J

jnc

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 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"
Click to expand...

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.
 
Back
Top