469,604 Members | 2,376 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,604 developers. It's quick & easy.

regular expresdions help required

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"


Jul 21 '05 #1
1 1361
> 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.

Jul 21 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by John . | last post: by
1 post views Thread by jnc | last post: by
4 posts views Thread by Együd Csaba | last post: by
7 posts views Thread by Billa | last post: by
3 posts views Thread by LordHog | last post: by
8 posts views Thread by Uwe Schmitt | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.