«

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.

[posted & mailed]

On 8 Dec 2003, Fletch wrote:

>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.

--
Jeff Pinyan RPI Acacia Brother #734 2003 Rush Chairman
"And I vos head of Gestapo for ten | Michael Palin (as Heinrich Bimmler)
years. Ah! Five years! Nein! No! | in: The North Minehead Bye-Election
Oh. Was NOT head of Gestapo AT ALL!" | (Monty Python's Flying Circus)

> On 8 Dec 2003, Fletch wrote:
>
>>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.

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

Remove dashes for address
Spam bait: private.php?do=newpm&u=

(Fletch) wrote in message news:<. com>...
> 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

(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.