JohnLinux JohnLinux - 7 months ago 23
Ruby Question

How do I write a regex for Excel cell range?

I need to validate that something is an Excel cell range in Ruby, i.e: "A4:A6". By looking at it, the requirement I am looking for is:

<Alphabetical, Capitalised><Integer>:<Integer><Alphabetical, Capitalised>


I am not sure how to form a RegExp for this.
I would appreciate a small explanation for a solution, as opposed to purely a solution.

A bonus would be to check that the range is restricted to within a row or column. I think this would be out of scope of Regular Expressions though.

I have tried
/[A-Z]+[0-9]+:[A-Z]+[0-9]+/
this works but allows extra characters on the ends.

This does not work because it allows extra's to be added on to the beginning or end:

"HELLOAA3:A7".match(/\A[A-Z]+[0-9]+:[A-Z]+[0-9]+\z/)
also returns a match, but is more on the right track.

How would I limit the number range to 10000?
How would I limit the number of characters to 3?

Answer

This will do both: match Excel range and that they must be same row or column. Stub

^([A-Z]+)(\d+):(\1\d+|[A-Z]+\2)$

A4:A6             // ok
A5:B10            // not ok
B5:Z5             // ok
AZ100:B100hello   // not ok

The magic here is the back-reference group:

([A-Z]+)(\d+)     -- column is in capture group 1, row in group 2
(\1\d+|[A-Z]+\2)  -- the first column followed by any number; or
                  -- the first row preceded by any character