Excel cell references in formulas

(?:\b|\$)     # identification begins at word boundary or $ character (fixed column reference)
              # non-capturing group (but the contents is included in the match)
[a-zA-Z]+     # column reference
\$?           # optional $ character (fixed row reference)
[0-9]+# row reference
\b(?!\()      # identification ends at row boundary that is not immediately followed by an
              # open parenthesis (which might signify a function)
              # we use negative lookahead to make sure that a parenthesis is NOT following immediately after)

              # the following part tries to identify that there is an even number of ' or " characters
              # following the identified text (0 is a solution, too);
              # if there's an odd number, than the cell reference is inside a quoted string and as such is ignored
              # strings without an ending ' or " , like: 'qwe L56
              # will have a match, but that shouldn't be a problem, since that is not a valid formula, so it would be
              # flagged by Excel

(?=# positive lookahead: the contents of this group should be found immediately after
 (?:           # non-capturing group
  [^"']*(?:"|') # any character but ' or ", as many times as possible, followed by ' or " [odd]
  [^"']*(?:"|') # again, any character but ' or ", as many times as possible, followed by ' or " [even]
 )*            # end of the non-capturing group; repeat as many times as possible
 [^"']*$# any character but ' or ", as many times as possible, up to the end of the string
)             # end of the positive lookahead

Identify Excel cell references in a formula. Include anchored cell references (like $I$34, or I$34 or $I34). Exclude functions (like YR1990(); basically any cell reference that has an open parenthesis immediately after) and things that look like cell references but are stored in strings ( enclosed between ' or " )

Ionut D
02/03/2015 14:16:22

0 comments so far.

Excel cell references in formulas

<< back to Library