2

Some valid sheeet names are as under among the other various names

t'#3cfbsdjfdsjb''''''''^^^^^^&&'!$C$8:$N$23   

1'!$E$13:$H$19    

!'!$B$7:$E$16    

@@@@@@@'!$B$7:$K$21

!$'!$B$12:$M$17

Iam using 

string pattern =@"[^<]+?\!\$[A-Z]+\$[0-9]+"

Regex r = new Regex(Constants.CELL_REFERENCE_PATTERN);
            Match m = r.Match(selectedVal);
            if (m.Success) return true;
            else return false;

but it is failing for

"t'#3cfbsdjfdsjb''''''''^^^^^^&&'!$C$8:$N$23,p,mv" or "sheeet1!$A1."

Thanks in advance

3 Answers3

5

Ignoring the C# factor, these are regular expressions that I have found to work.

Cell Reference with sheet optional (<sheet>!<cell>)

"(('[^/\\?*\[\]]{1,31}'|[A-Za-z0-9_]{1,31})!)?((\$?[A-Za-z]{1,3})(\$?[0-9]{1,6}))"

Notes:

  • This regex does not validate Cell values, ex. ZZZ999999 is valid through the regex, but it is not a valid location inside of Excel.
  • This is for Excel 2007+. For pre-2007, change {1,3} to {1,2} and {1,6} to {1,5}. This is because the range of cells went from IV65536 (pre-2007) to XFD1048576 (2007+). But of course, there is no validation and technically it could be [A-Za-z]+ and [0-9]+.
srbs
  • 634
  • 9
  • 12
  • `A-Za-z_` doesn't work for "Sheet1" you need digits as well (so A-Za-z1-9_) – flutefreak7 Dec 01 '16 at 05:27
  • 1
    @flutefreak7, good catch! I updated it and included support for `0` – srbs Dec 22 '16 at 01:48
  • I was thinking `\w` might work, but apparently it varies in meaning and even in .Net requires some options to limit its behavior to only alpha-nuneric and underscore rather than including other Unicode characters. http://stackoverflow.com/a/336220/1639671. While I'm here... If the sheet has spaces you'll also need `''` around the sheet... – flutefreak7 Dec 22 '16 at 01:53
  • 1
    @flutefreak7, that's exactly why I explicitly defined the character classes. It's a very common issue with regex that most people don't know or remember off hand. As for the spaces, it's taken care of in the first half of the sheet section: `'[^/\\?*\[\]]{1,31}'` – srbs Dec 22 '16 at 02:01
  • still missing a lot: such as space, or column scope C:D, rows scope 1:99... – Nguyen Manh Mar 15 '22 at 23:12
1

I do not know a lot about C# but in most Regexp API Match and Search are distinct.

  1. Match means that the string have to match the whole pattern
  2. Search means that the string contains the pattern

In your case 

@"[^<]+?\!\$[A-Z]+\$[0-9]+"

"t'#3cfbsdjfdsjb''''''''^^^^^^&&'!$C$8:$N$23,p,mv"

Probably match very probably only 

"t'#3cfbsdjfdsjb''''''''^^^^^^&&'!$C$8"

Try this Regex instead : @"[^<]+?!\$[A-Z]+\$[0-9]+.*"

Usually when I do not understand why a Regex does not work I split it in several simpler part.

It is also a good practice to test them in a tiny Unit Test.

VGE
  • 4,171
  • 18
  • 17
0

If you run into this while looking for office-js / general JavaScript solution, use this one:

('.{1,31}'|[\w.]{1,31}?)!(([\w$].*?):([\w$]*)|([\w$].*))

Below is a link to a unit test with the author's sheet names. https://regex101.com/r/IDF2g6/6

Jolleyboy
  • 1,293
  • 11
  • 18