REGEX to match only VALID EXCEL COLUMNS (A-XFD)

Ok, I have ANOTHER REGEX question for everyone.

I have a field that has multiple validation steps. The first step is to ensure it isn’t blank, second is to ensure that only between 1 and 3 CAPITAL LETTERS are entered, and third, to ensure it doesn’t contain “[” (an opening bracket will denote a function in this scenario and we skip validation). All of this works. Now, I have to ensure that the value being entered is only a valid Excel Column Reference. Valid Excel Columns can be the letters A – XFD.

I am currently using:

if (checkValue !==""){   //check for not blank
    if ((checkValue.match(/^[A-Z]{1,3}$/) === null) && (functionCheck === false) && (validateColumnRange(rangeFrom))) { //check for only 1 - 3 alpha chars & run function check (function looks for "["), and run function check to validate column range

    //do A - XFD validation here

    }
}

any further direction will be much appreciated as I have been through regex tuts for hours now and am lost.

I had been given help on a similar issue in the past and my poor attempt to emulate the function that was provided then is as follows:

function validateColumnRange(valueRange) {
    if (typeof valueRange !== "string" || !valueRange.length)
        return false;
    var startIndex = valueRange.search(/[d+]/);
    var column = valueRange.substring(0, startIndex).toUpperCase();
    return (column >= "A" && column <= "XFD");
}

it doesn’t work…please help

Answer

Since you’ve already determined that the value is 1-3 alphabetic characters, how about:

(column.length < 3 || column <= "XFD")

Any value with 2 or less letters should be acceptable, and for the case of three letters, alphabetic comparison is adequate.

Advertisement

Answer