Validate Excel Range with RegEx

Discussion in 'Perl Misc' started by Fletch, Dec 8, 2003.

  1. Fletch

    Fletch Guest

    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.
     
    Fletch, Dec 8, 2003
    #1
    1. Advertising

  2. [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)
     
    Jeff 'japhy' Pinyan, Dec 8, 2003
    #2
    1. Advertising

  3. > 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: mailto:
     
    A. Sinan Unur, Dec 8, 2003
    #3
  4. (Fletch) wrote in message news:<>...
    > 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
     
    James E Keenan, Dec 8, 2003
    #4
  5. Fletch

    Jay Tilton Guest

    (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.
     
    Jay Tilton, Dec 8, 2003
    #5
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. zxo102
    Replies:
    3
    Views:
    3,323
    Stuart Corrie
    Aug 9, 2005
  2. =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=

    Problem with Excel reports ::::Excel 2003 Migration To Excel 2007

    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=, Oct 5, 2007, in forum: ASP .Net
    Replies:
    15
    Views:
    1,602
    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=
    Oct 24, 2007
  3. Replies:
    3
    Views:
    832
    Reedick, Andrew
    Jul 1, 2008
  4. Todd Burch
    Replies:
    4
    Views:
    320
    bbiker
    Jul 18, 2007
  5. Rob Wahmann

    validate numeric range 6-10 digits

    Rob Wahmann, Feb 9, 2004, in forum: Javascript
    Replies:
    4
    Views:
    143
    Evertjan.
    Feb 9, 2004
Loading...

Share This Page