AlphaWolf AlphaWolf - 5 months ago 13
Javascript Question

Regex Expression that Matches Excel-like Reference Cells

I am trying to make a regex that only matches on the letters between the range of A-Z (inclusive) followed by a number between 0-100 (inclusive). If there are any numbers before my regex, I do not want it to match. For example: 5A1 should not match because there is the number 5 before it, even though A1 is in the string. Likewise, I do not want it to match if there are any number values after that range such as A10000 (shouldn't match because 1000 is outside of 100).

Below demonstrates what I am trying to match on:

A34A1 // no match
5A1 // no match
2.344A1 // no match
A1001 // no match
A1 // match A1
A10 // match A10
A100 // match A100
SUM(A1:A2) // match A1 and A2
SUM(A1:A2, 5A1, A3) // match A1,A2, A3

I have been trying these regex expressions but am unsure of how to exclude any numbers before or after that range:




Use this regex:


or simpler equivalent:


Using the \b metacharacter at the start and end of the regex, you eliminate any undesirable preceding or trailing characters. The [A-Z] character class includes only a single uppercase letter to start the cell reference. The numeric portion is handled by alternation of either 0-99 or 100, where the 0-99 portion is handled by one to two digit character classes. In place of the explicit [0-9] character classes, the \d metacharacter would work just as well, if that's your preference.

Here's a sample Ruby program (similar regex flavor to Javascript) to show it working:

data = [
  [ "A34A1",   false ],
  [ "5A1",     false ],
  [ "2.344A1", false ],
  [ "A1001",   false ],
  [ "A1",      true ],
  [ "A10",     true ],
  [ "A100",    true ],
  [ "SUM(A1:A2)", true ],
  [ "SUM(A1:A2, 5A1, A3)", true ]

data.each do |pair|
  puts "#{pair[1] == !(pair[0] =~ /\b[A-Z]([0-9]{1,2}|100)\b/).nil? ? 'Pass' : 'Fail'}: '#{pair[0]}'"

In the data array, the second element of each pair is a boolean, which determines whether the test cell reference should match or not.

The output of running this program shows all tests pass:

> ruby regex_test.rb
Pass: 'A34A1'
Pass: '5A1'
Pass: '2.344A1'
Pass: 'A1001'
Pass: 'A1'
Pass: 'A10'
Pass: 'A100'
Pass: 'SUM(A1:A2)'
Pass: 'SUM(A1:A2, 5A1, A3)'