81370

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:

<ol> <li>Sheet1!A1</li> <li>'Sheet!!'!A1</li> </ol>

I am using following expression but it is not working.

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

Answer1:

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.

<strong>Edit</strong>: 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.

Answer2:

Oh some other problem.

Its working now

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

Answer3:

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

Recommend

  • Excel summing a text value of an adjacent cell
  • How to validate a phone number
  • Regex for alphanumeric and the + character
  • Check if there is phone number in string C#
  • HowTo for newbie: Managing currency in iPhone app
  • Unit Testing Spring MVC Controller that returns PagedResources
  • Fill value backwards from occurence by group
  • Detecting single one-bit streams within an integer
  • How to get rows with min values in one column, grouped by other column, while keeping other columns?
  • Conditionally ignore primitive typed fields with Jackson
  • Fortran: Array of arbitrary dimension?
  • How to write .bin file in R and open it in Fortran
  • OpenXML how to get cell in range
  • How to run a query between dates and times?
  • Tips for creating scalable WPF user control
  • How to calculate total Fridays between two dates in excel on weekly, biweekly and monthly basis?
  • Fixed Background Works in Chrome but Not Firefox?
  • Excel distinct count with conditions
  • Responsive Form on top of Responsive Image? - Bootstrap
  • multidatatrigger with multibinding in ControlTemplate.Triggers
  • Is there a parser equivalent of 'fragment' marking in ANTLR4?
  • How to open html table in xls on click of a button
  • several dataProvider per one Test in TestNG
  • NUnit 3.0 TestCase const custom object arguments
  • Plotting line graph with factors in R
  • Build Successful but not running on simulator
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • Extracting HTML between tags
  • FFmpeg Conversion Error
  • MongoDB in PHP using aggregate to group by _id is null not working
  • Regex thinks I'm nesting, but I'm not
  • VBA Convert delimiter text file to Excel
  • Traverse Array and Display in markup
  • Transpose CSV data with awk (pivot transformation)
  • Data Validation Drop Down Box Arrow Disappearing
  • Matrix multiplication with MKL
  • Why can't I rebase on to an ancestor of source changesets if on a different branch?
  • Hits per day in Google Big Query
  • File not found error Google Drive API
  • Converting MP3 duration time