JohnLinux JohnLinux - 1 year ago 115
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
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:

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 Source

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


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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download