Validate a gsheet/excel relative range string using js regex

Question

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)})

Answer 1

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