Is there any regular expression pattern to match a excel sheet name and address i.e. cell reference

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

---

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]+.

---

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.

---

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