Check if a Python string is a valid Excel cell

user_11161432 0
Viewed 837

Given some alphanumerical strings in Python such as

  • A9
  • B44B
  • C101
  • 4D4

how do I check if the string is a valid Excel cell (i.e., letters come before numbers)?

I've tried using the .isalpha and .isdigit methods to "gather" letters and numbers, and then using .index to check whether all letters appear before numbers, but my logic is becoming too complex, and I feel like I'm not accounting for all possibilities.

Is there a simple way to achieve this?

Expected result:

>>> is_valid_excel_cell('A9')
True
>>> is_valid_excel_cell('B44B')
False
>>> is_valid_excel_cell('C101')
True
>>> is_valid_excel_cell('4D4')
False
openpyxl excel python string
3 Answers

As per my comment, validity depends on Excel version. Newer versions have a column range of A-XDF and rows from 1-1048576. It might not be necessary in your project, but for future reference it could be handy:

Regex pattern: ^([A-Z]{1,2}|[A-W][A-Z]{2}|X[A-E][A-Z]|XF[A-D])([1-9]\d{0,6})$

To visualize this:

Regular expression visualization

First group captures the column reference for Excel 2010 and higher which is A-XDF, and the second group captures the numeric part which should always start with 1-9 followed by 0 to 6 characters but cannot exceed 1048576.

So in full effect you could think about:

import re
def is_valid_excel_cell(c):
    m = re.match(r'^([A-Z]{1,2}|[A-W][A-Z]{2}|X[A-E][A-Z]|XF[A-D])([1-9]\d{0,6})$',c)
    return bool(m) and int(m.group(2)) < 1048577
user_97581940

I would use regular expressions, well fit for the task:

import re

def is_valid_excel_cell(c):
    m = re.match("[A-Z]+\d+$",c)
    return bool(m)

that one checks if the cell contents starts with a capital letter and ends with a digit.

Now if range check is required on the number one more step would be required, one can extract the digits and convert them to integer, compare to a range (I'll let the reader adjust the range, as I'm not a excel expert).

def is_valid_excel_cell(c):
    m = re.match("[A-Z]+(\d+)$",c)
    return bool(m) and m.group(1).isdigit() and 0 < int(m.group(1)) < 16384
user_64515730 
import re

def is_valid_excel_cell(addr):
    m = re.match(r'^([A-Z]{1,3})([1-9]\d*)$', addr)
    if not m:
        return False
    letters, numbers = m.groups()
    if len(letters) == 3 and letters > 'XFD':
        return False
    if int(numbers) > 1048576:
        return False
    return True

Semicompressed for Python 3.8+ only (due to the use of the walrus (:=) operator):

def is_valid_excel_cell(addr):
    return (bool(m := re.match(r'^([A-Z]{1,3})([1-9]\d*)$', addr)) and
        (len(m.group(1)) < 3 or m.group(1) <= 'XFD') and 
        int(m.group(2)) <= 1048576)
user_13224010
Related