preg_match regexp for Excel cell pattern

[radi8]

I have been playting with the following preg_match test for testing user input for excel cell location. The input should be of the form:

a1:b20, or a1:zz64564 (or something like that). What the individual cells cannot be is: aaa1 (or 3 alpha chars at the beginning).

 

To test this pattern (a1:b1) I am first exploding the string to get each element into the array, and then testing each reference individually. I will combine it later once I get the regex pattern correct.

 

What is not working is when the cell has more than 2 alpha chars at the beginning.

 

If someone can help clean this up, I would appreciate it. These patterns are hard to get right if you are a novice at them.

 

$regex='/(^([A-Z])|([a-z]){1,2}[0-9]{1,})/';
        if(isset($_POST['startingCell'])){
            $range=explode(':',$_POST['startingCell'],2);
            if(strlen($range[0])>0 && count($range)==2){
                $validArray=true;
                foreach($range as $rng){ 
                    if(!preg_match($regex,$rng)){
                        $frmErrorLevel=3;
                       $frmErrorRange=true;
                    }
                    else{
                        $validArray=true;
                    }
                }
            }
            else{
                $frmErrorLevel=3;
                $frmErrorRange=true;
            }
        }

[.josh]

can you provide more explanation about what the overall rules of the format is? From your post, it looks like you want

 

1 or 2 letters, followed by 1 or more numbers, followed by a colon, followed by 1 or 2 letters, followed by 1 or more numbers.  Is that right?

 

if (preg_match('~^[a-z]{1,2}[0-9]+:[a-z]{1,2}[0-9]+$~i',trim($_POST['startingCell']))) {
  // correct format
} else {
  // not correct format
}

[radi8]

I apologize for the confusion. When all is said and done, the user will enter a range in the form of 'A1:B5'. The user will enter only 1 range of values.

 

In this range, you have a beginning cell and ending cell, which in the script above I explode into the array so that I have:

$range: Array
(
    [0] => A1
    [1] => B5
)

I am trying to create the regex pattern to validate each cell reference so that it has 1 or 2 alpha chars at the beginning, and 1 to 5  numeric chars at the end. The alpha chars MUST be at the beginning and the numeric chars MUST be at the end.

 

I have this and it seems to be working better but further testing is required:

$regex='/^([A-Za-z]{1,2}+[0-9]{1,5})/';

[50jkelly]

Hi radi8.  The expression you posted worked quite well, the only problem I found was that if the numeric portion of the string was more than 5 characters, a match would still be returned of the first 5 characters.  For example, B123456 would return a match for B12345.  This is easily solved with a $ at the end of the expression.  Here's what I ended up with:

/^[a-z]{1,2}[0-9]{1,5}$/i

[radi8]

50jkelly, well that could be a problem since Excel 2003/2005 limits are 64565 rows. I need to do a test to ensure that this limit is not exceeded. Excel 2010 allow for 104876 rows, but this is only on newer versions.

 

Thanks, I will add the $ at the end of the string and give it a shot!

[.josh]

if all you are doing is exploding at the : and then checking each bit individually and ultimately throwing an error if either one fails to match, then you can skip the explode and individual checking and use the pattern/condition I supplied.  The only adjustment would be to limit the numbers to up to 5 instead of just one or more, so:

 

if (preg_match('~^[a-z]{1,2}[0-9]{1,5}:[a-z]{1,2}[0-9]{1,5}$~i',trim($_POST['startingCell']))) {
  // correct format, do whatever here
} else {
  // not correct format, throw error or whatever
}

 

The only reason I can see for you to continue to explode and check each part individually, is if you were wanting to have a more specific error, like "starting cell is not right" vs. more generic "range as a whole is not right"

[radi8]

if all you are doing is exploding at the : and then checking each bit individually and ultimately throwing an error if either one fails to match, then you can skip the explode and individual checking and use the pattern/condition I supplied.  The only adjustment would be to limit the numbers to up to 5 instead of just one or more, so:

 

if (preg_match('~^[a-z]{1,2}[0-9]{1,5}:[a-z]{1,2}[0-9]{1,5}$~i',trim($_POST['startingCell']))) {
  // correct format, do whatever here
} else {
  // not correct format, throw error or whatever
}

 

The only reason I can see for you to continue to explode and check each part individually, is if you were wanting to have a more specific error, like "starting cell is not right" vs. more generic "range as a whole is not right"

 

This is the goal, but I was going to get the individual regex pattern correct first. Adding the ':' is a trivial matter from that point.

 

Thank you all for the input, I have it working and all is golden!!!