CODEWITHSUNDEEP
c#regex
meetjaydeep
meetjaydeep

Reputation: 1850

How can I validate an Excel cell reference that includes a sheet name?

I want to validate a cell reference in an Excel sheet formula.

Valid strings:

  1. Sheet1!A1
  2. 'Sheet!!'!A1

I am using following expression but it is not working.

"^='?[^[]/\*]*'?!+[A-Z]+[0-9]+$"

Upvotes: 2

Views: 2499

Answers (3)

Jaroslav Jandek
Jaroslav Jandek

Reputation: 9563

Regex re = new Regex(@"^=('?[^']+'?|[^!]+)![A-Z]+[0-9]+$");
bool isOk = re.IsMatch("=Sheet1!A1");

Change the [^!] to whatever characters you do not want in the sheet's name.

It will match anything with the format ='<anything>'!XN or =<anything>!XN where X is at least one uppercase letter and N is at least one digit.

Edit: I am not sure if OP wants the = character at the start or not (the original post had it). If you don't, remove the = character from the start of the regular expression.

Upvotes: 3

meetjaydeep
meetjaydeep

Reputation: 1850

Oh some other problem.

Its working now

^='?[^/\[]*]*'?!{1}[A-Z]+[0-9]+$

Upvotes: 1

Felice Pollano
Felice Pollano

Reputation: 33272

this one should work: =?(\s|\w)+![A-Za-z]+\d+

Upvotes: 0

Related Questions