Parsing Excel reference with regular expression?

Question

Excel returns a reference of the form

=Sheet1!R14C1R22C71junk

("junk" won't normally be there, but I want to be sure that there's no extraneous text.)

I would like to 'split' this into a VB array, where

a(0)="Sheet1"
a(1)="14"
a(2)="1"
a(3)="22"
a(4)="71"
a(5)="junk"

I'm sure it can be done easily with a regular expression, but I just can't get the hang of it. Is there a kind soul who could help me?

Thanks

Answer 1

=([^!]+)!R(\d+)C(\d+)R(\d+)C(\d+)(.*)

should work.

[^!]+ matches a sequence of non-exclamation-point characters.

\d+ matches a sequence of digits.

.* matches anything.

So, in VB.NET:

Dim a As Match
a = Regex.Match(SubjectString, "=([^!]+)!R(\d+)C(\d+)R(\d+)C(\d+)(.*)")
If a.Success Then
    ' matched text: a.Value
    ' backreference n text: a.Groups(n).Value
Else
    ' Match attempt failed
End If

Answer 2

A straightforward String.Split would work, provided the "junk" text wasn't there:

Dim input As String = "=Sheet1!R14C1R22C71"
Dim result = input.Split(New Char() { "="c, "!"c, "R"c, "C"c }, StringSplitOptions.RemoveEmptyEntries)
For Each item As String In result
    Console.WriteLine(item)
Next

The regex gets a little tricky since you will need to go through the Groups and Captures of the nested portions to get the proper order.

EDIT: here's my regex solution. It accepts multiple occurrences of R's and C's.

Dim input As String = "=Sheet1!R14C1R22C71junk"
Dim pattern As String = "=(?<Sheet>Sheet\d+)!(?:R(?<R>\d+)C(?<C>\d+))+"

Dim m As Match = Regex.Match(input, pattern)
If m.Success Then
    Console.WriteLine(m.Groups("Sheet").Value)
    For i = 0 To m.Groups("R").Captures.Count - 1
        Console.WriteLine(m.Groups("R").Captures(i).Value)
        Console.WriteLine(m.Groups("C").Captures(i).Value)
    Next
End If

Pattern explanation:

• "=(?Sheet\d+)" : matches an = sign followed by "Sheet" and digits. Uses named group of "Sheet"
• "!(?:R(?\d+)C(?\d+))+" : matches the exclamation mark followed by at least one occurrence of the *R*xx*C*xx portion of the text. Named groups of "R" and "C" are used.
• "(?:...)+" : this portion from the above portion matches but does not capture the inner pattern (i.e., the R/C part). This is to avoid unnecessarily capturing them while we are actually capturing them with the named groups.