Regular expression to validate Excel cell

Question

I'm working on a php application where the user has to insert an Excel's cell id (for example A1 or AB32), a value which is stored in the database for later use and I'm trying to validate the cell id format using a regular expression, but it just doesn't seem to be working, this is what I've got so far.

^[a-zA-Z]\d$

Ormolu

You need quantifiers if there can be multiples. [a-zA-Z]+ will allow AB, or A, or aBc, etc. \d+ will allow 1, 32, 333, etc. You can use regex101 to test, regex101.com/r/8OQVBs/1, with update regex101.com/r/8OQVBs/2 – Heddy

Use ^[a-zA-Z]+\d+$ or even ^[a-zA-Z]+\d+\z – Priscillaprise

BTW, I have checked: in my Excel, I have max 1048576 rows and XFD columns. This might not be that easy with a regex. – Priscillaprise

@WiktorStribiżew - I had a go - see my answer below. – Lyons

Answer 1

There's an awesome answer in this question by @BartKiers where he builds a function to construct these type of regexes that need to match ranges of x to y. His logic transfers nicely to text ranges and is tested in PCRE dialect at regex101.com.

The regex:

^(?:[A-Z]|[A-Z][A-Z]|[A-X][A-F][A-D])(?:[1-9]|[1-9][0-9]|[1-9][0-9][0-9]|[1-9][0-9][0-9][0-9]|[1-9][0-9][0-9][0-9][0-9]|[1-9][0-9][0-9][0-9][0-9][0-9]|10[0-3][0-9][0-9][0-9][0-9]|104[0-7][0-9][0-9][0-9]|1048[0-4][0-9][0-9]|10485[0-6][0-9]|104857[0-6])$

Which basically says:

• Columns part: A-Z, or A-Z with A-Z, or A-X with A-F with A-D

• Rows part: 1-9, or 1-9 with 0-9, or 1-9 with 1-9 with 0-9 etc all the way to the max of 104857 with 0-6

It matches the following:

A1
AA11
AAA111
ZZ12
YY1048575
XFD1048576

It will not match the following:

A0
AA01
AAZ1111111
XFD1048577
XFE1048576
ZZZ333
ZZZ9999999

Here's the diagram:

Lyons

Answer 2

try this ^[a-zA-Z]{1,4}(\d+)$

• ^([a-zA-Z]+) : starting between 1 and 4 letters
• (\d+)$ : ending with one digit or more

Styx

How do you validate that the digit part is less than 1048577?? – Antho

How do you validate that the letter part has less than 4 letters?? – Antho

I just updated my answer to check for a max of 4 letters. But as long as i know regex is for strings, it can't evaluate values, and your constraint can't lead to a specific pattern that can be "easily" implemented as an expression. you can try to split you max limit 1048577 to many conditions (1 or nothing), (0 to 9 or noting) ... but event this way you can't get exactly the correct limit. – Styx

Answer 3

Maybe ^([A-Z]{1,3})([1-9]\d{0,6})$

or A1 ~ XDF1048576:

"^(?:[A-Z]{1,2}|" +                  // A   - ZZ
"[A-W][A-Z][A-Z]|" +                 // AAA - WZZ
"X[A-E][A-Z]|" +                     // XAA - XEZ
"XF[A-D])" +                         // XFA - XFD
"(?:[1-9][0-9]{0,5}|" +              // 1       - 999999
"10[0-3][0-9][0-9][0-9][0-9]|" +     // 1000000 - 1039999
"104[0-7][0-9][0-9][0-9]|" +         // 1040000 - 1047999
"1048[0-4][0-9][0-9]|" +             // 1048000 - 1048499
"10485[0-6][0-9]|" +                 // 1048500 - 1048569
"104857[0-6])$"                      // 1048570 - 1048576

Stylus