CODEWITHSUNDEEP
javascriptregex
Code Guy
Code Guy

Reputation: 3198

Validate a gsheet/excel relative range string using js regex

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

Upvotes: 2

Views: 151

Answers (3)

RavinderSingh13
RavinderSingh13

Reputation: 133760

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+:\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.
(?:                  ##Starting one and only non-capturing group here.
  [a-zA-Z]+\d*:      ##matching 1 or more alphabets followed by 0 or more digits followed by colon.
  [a-zA-Z]+\d*       ##matching 1 or more alphabets followed by 0 or more digits here.
  |                  ##Putting OR condition here.
  [a-zA-Z]+[1-9]+\d* ##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 non-capturing group at the end of the value here.

Upvotes: 3

SaSkY
SaSkY

Reputation: 1086

Try this:

^([A-Za-z])\1*(?:[1-9]\d*)*:([A-Za-z])\2*(?:[1-9]\d*)*$|^(?:([A-Za-z])\3*|[1-9]\d*:)[1-9]\d*$

Explanation:

First part ^([A-Za-z])\1*(?:[1-9]\d*)*:([A-Za-z])\2*(?:[1-9]\d*)*$ to match things like C:F, A10:B10, A:B10, A10:B, A:B and B9:B10.

  • ^ start of the line/string.

  • ([A-Za-z]) Group1: [A-Za-z] one letter captured by the first group, and the value inside the first capturing group can be accessed by \1.

  • \1*, \1 the value inside the first capturing group, * match zero or more of the value inside the first capturing group, for example if the value is A, then things like AAA will be matched.

  • (?:[1-9]\d*)* non-capturing group, [1-9] one digit from 1 to 9, followed by \d* zero or more digits from 0 to 9, and that group will be repeated between zero or more times.

  • : a literal colon :.

  • ([A-Za-z]) Group2:[A-Za-z] the same as before with Group1, but now we can access the value inside the second capturing group by \2.

  • \2* the same as before with \1*, but now the value is the one inside the second capturing group.

  • (?:[1-9]\d*)* the same as before.

  • $ the end of the line/string.

  • | the alternation operator it is like Boolean OR, to match other cases.

Second part ^(?:([A-Za-z])\3*|[1-9]\d*:)[1-9]\d*$ to match things like A1, B1, A2 and 10:10.

  • ^ the start of the line/string.

  • (?:([A-Za-z])\3*|[1-9]\d*:) non-capturing group contains:

    • ([A-Za-z]) Group3:[A-Za-z] the same as before with Group1 and Group2, but now we can access the value inside the third capturing group by \3.
    • \3* the same as before with \1* and \2*, but now the value is the one inside the third capturing group.
    • | the alternation operator it is like Boolean OR, to match other cases.
    • [1-9]\d*: one digit from 1 to 9, followed by zero or more digits from 0 to 9, followed by a literal :.

  • [1-9]\d* one digit from 1 to 9, followed by zero or more digits from 0 to 9.

  • $ the end of the line/string.

See regex demo

Upvotes: 2

Peter Thoeny
Peter Thoeny

Reputation: 7616

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

Upvotes: 4

Related Questions