Validate a gsheet/excel relative range string using js regex-CodePudding

I want to validate a range using javascript
Any range that has $ symbol must say invalid
Any range string that has symbols other than : must say invalid
Accpetable characters are capital or small alphabets and 0-9 numbers

Some of the expected results

A0  // invalid range begins with 1 
ZZ12    // valid  
ZZ12:   // invalid incorrectly terminated 
:A11    // invalid incorrectly started 
:1  // invalid incorrectly started 
:A  // invalid incorrectly started 
A1  // valid  
B1  // valid  
A2  // valid  
C:F // valid  
A10:B10 // valid  
A:B10   // valid  
A10:B   // valid  
A:B // valid  
10:10   // valid  
AA1 // valid  
AAA1    // valid  
B9:B10  // valid  
A   // invalid incomplete range string 
1   // invalid incomplete range string 
B   // invalid incomplete range string 
20  // invalid only a number not allowed 
@   // invalid symbols not allowed 
##  // invalid symbols not allowed

I have tried with

["A0","ZZ12","ZZ12:",":A11",":1",":A","A1","B1","A2","C:F","A10:B10","A:B10","A10:B","A:B","10:10","AA1","AAA1","B9:B10","A","1","B","20","@","##"]
.map(zz=>{return zz   "--->"   /^[A-Z]?[0-9]?:[A-Z]?[0-9]?$/.test(zz)})

CodePudding user response：

This solution should address all your requirements, also for non-zero numbers:

const regex = /^(?:[A-Z] [1-9][0-9]*|[A-Z] (?:[1-9][0-9]*)?:[A-Z] (?:[1-9][0-9]*)?|[1-9][0-9]*:[1-9][0-9]*)$/;

["A0","ZZ12","ZZ12:",":A11",":1",":A","A1","B1","A2","C:F","A10:B10","A:B10","A10:B","A:B","10:10","1:999","0:1","AA1","AAA1","B9:B10","A","1","B","20","@","##"
].map(str => {
  //let valid = regex.test(str);
  let valid = str.match(regex);
  console.log(str, '==>', valid ? 'ok' : 'invalid');
});

Explanation of regex:

^(?: -- start of string and non-capture group start
- [A-Z] [1-9][0-9]* -- 1 letters, number starting from 1
| -- logical or
- [A-Z] (?:[1-9][0-9]*)?:[A-Z] (?:[1-9][0-9]*)? -- 1 letters, optional number starting from 1, :, 1 letters, optional number starting from 1
| -- logical or
- [1-9][0-9]*:[1-9][0-9]* -- 1 letters, number starting from 1, :, 1 letters, number starting from 1
)$ -- end of non-capture group and string

CodePudding user response：

With your shown samples please try following regex and JS code. Here is the Online Demo for used regex.

const regex = /^(?![$])((?:[a-zA-Z] \d*):(?:[a-zA-Z] )\d*|[a-zA-Z] [1-9] |\d :\d )$/;

[
"A0",
"ZZ12",
"ZZ12:",
":A11",
":1",
":A",
"A1",
"B1",
"A2",
"C:F",
"A10:B10",
"A:B10",
"A10:B",
"A:B",
"10:10",
"AA1",
"AAA1",
"B9:B10",
"A",
"1",
"B",
"20",
"@",
"##",
"$"
].forEach(element => 
    console.log(`${element} ----> ${regex.test(element)}`)
);

Explanation: Adding detailed explanation for above used regex.

^                   ##Checking from starting of value here.
(?![$])             ##Checking negative lookahead to make sure it doesn't have $ in it.
(                   ##Starting one and only capturing group here.
 (?:[a-zA-Z] \d*):  ##In a non-capturing group matching 1 or more alphabets followed by 0 or more digits followed by colon.
 (?:[a-zA-Z] )\d*   ##In a non-capturing group matching 1 or more alphabets followed by 0 or more digits.
 |                  ##Putting OR condition here.
 [a-zA-Z] [1-9]     ##Matching 1 or more alphabets followed by 1 or more digits.
 |                  ##Putting OR condition here.
 \d :\d             ##Matching 1 or more digits followed by colon followed by 1 or more digits.
)$                  ##Closing capturing group at the end of the value here.