Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > Validate Excel Range with RegEx

Reply
Thread Tools

Validate Excel Range with RegEx

 
 
Fletch
Guest
Posts: n/a
 
      12-08-2003
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.
 
Reply With Quote
 
 
 
 
Jeff 'japhy' Pinyan
Guest
Posts: n/a
 
      12-08-2003
[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)

 
Reply With Quote
 
 
 
 
A. Sinan Unur
Guest
Posts: n/a
 
      12-08-2003
> 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
http://www.velocityreviews.com/forums/(E-Mail Removed)
Remove dashes for address
Spam bait: (E-Mail Removed)
 
Reply With Quote
 
James E Keenan
Guest
Posts: n/a
 
      12-08-2003
(E-Mail Removed) (Fletch) wrote in message news:<(E-Mail Removed). 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
 
Reply With Quote
 
Jay Tilton
Guest
Posts: n/a
 
      12-08-2003
(E-Mail 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.

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How make regex that means "contains regex#1 but NOT regex#2" ?? seberino@spawar.navy.mil Python 3 07-01-2008 03:06 PM
Problem with Excel reports ::::Excel 2003 Migration To Excel 2007 =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?= ASP .Net 15 10-24-2007 01:34 PM
regex help please - parsing an Excel Range Todd Burch Ruby 4 07-18-2007 09:43 PM
Python-Excel: How to paste selected cells (range) to different location on the same sheet in Excel zxo102 Python 3 08-09-2005 02:41 PM
validate numeric range 6-10 digits Rob Wahmann Javascript 4 02-09-2004 04:28 PM



Advertisments