Validate Excel Range with RegEx

F

Fletch

Any thoughts on how to validate an Excel range with RegEx?
Acceptable inputs would include $A1:$BD25, C:C, B4 etc.
I'm close to coming up with an answer but I'm not sure how to stop
invalid range references such as C3:A2 from being accepted.

Thanks.
 
J

Jeff 'japhy' Pinyan

[posted & mailed]

Any thoughts on how to validate an Excel range with RegEx?
Acceptable inputs would include $A1:$BD25, C:C, B4 etc.

What does that first range mean? Can you give an exhaustive list of
samples for Excel ranges?
I'm close to coming up with an answer but I'm not sure how to stop
invalid range references such as C3:A2 from being accepted.

Well, do you need to use a regex to enforce the logic as well? If you can
permit a regex to be used to capture the PARTS of the range, and then use
logic to compare them, then it shouldn't be too hard, you'll just need a
regex with the ability to *parse* Excel ranges.
 
A

A. Sinan Unur

You know Excel itself does accept such ranges from the user. It just
quitely turns them into A2:C3.
 
J

James E Keenan

Any thoughts on how to validate an Excel range with RegEx?
Acceptable inputs would include $A1:$BD25, C:C, B4 etc.
I'm close to coming up with an answer but I'm not sure how to stop
invalid range references such as C3:A2 from being accepted.

Thanks.

I don't know the full definition of a valid range in Excel, so take
the following only as a crude first pass:

my @ranges = qw( C3:A2 C3:C2 C3:C3 C3:C4 C3:CC3 );
for (@ranges) {
my @temp = split(/:/, $_, 2);
$temp[0] gt $temp[1] ? print "Invalid: $_\n" : print "Valid:
$_\n";
}

HTH!

Jim Keenan
 
J

Jay Tilton

(e-mail address removed) (Fletch) wrote:

: Any thoughts on how to validate an Excel range with RegEx?
: Acceptable inputs would include $A1:$BD25, C:C, B4 etc.
: I'm close to coming up with an answer but I'm not sure how to stop
: invalid range references such as C3:A2 from being accepted.

What makes "C3:A2" an invalid range specification? Excel accepts it
without any complaint.

A set of row/column adresses is not the only way to specify a range.
Range("foo") is perfectly acceptable syntax for referring to a named range.

Validating the argument with a regex is fine if you want to impose
arbitrary restrictions on its format. You first need to decide exactly
what formats are valid, which means "etc." is right out.

If you simply want to catch arguments that will make Excel puke, you're
better off not wanting that. Just use the argument as it is and trap
errors thrown by Excel.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top