Python | Test if number is valid Excel column
Sometimes, while working with Python strings, we can have a problem in which we need to test for string if it’s a valid Excel column. This has application in many domains including day-day programming, web development, and Data Science. Let us discuss certain ways in which this task can be performed.
Method 1: Using re.match() + group()
The combination of the above functions can be used to perform this task. In this, we perform regex match() to match with the potential updated excel version which have A-XDF, and starts with 0-9 followed by 0-6 characters not exceeding 1048576. The groupby() is used to group the elements on that basis.
Python3
import re
test_str = "C101"
print ( "The original string is : " + test_str)
temp = 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})$' , test_str)
res = bool (temp) and int (temp.group( 2 )) < 1048577
print ( "Is string valid excel column : " + str (res))
|
Output :
The original string is : C101
Is string valid excel column : True
Time Complexity: O(n)
Auxiliary Space: O(n)
Method 2: Using Column Letter to Number Conversion
Explanation:
- We first initialize the input string test_str.
- We use a for loop to iterate through each character in the input string except the last character (which represents the row number). Inside the loop, we convert each character to a corresponding number between 1 and 26 using the ord() function (which returns the ASCII code of a character) and subtract 64 (the ASCII code of ‘A’ minus 1). We then multiply this number by 26 raised to the appropriate power based on the position of the character in the input string (e.g., the leftmost character is multiplied by 26 raised to the power of len(test_str)-2, the second-leftmost character is multiplied by 26 raised to the power of len(test_str)-3, etc.). We add up these products to obtain the column number.
- We check if the column number is between 1 and 16384 (the maximum number of columns in Excel). If it is, we set is_valid to True.
- Finally, we print the value of is_valid.
Python3
test_str = "C101"
print ( "The original string is : " + test_str)
is_valid = False
col_num = 0
for i in range ( len (test_str) - 1 ):
col_num + = ( ord (test_str[i]) - 64 ) * 26 * * ( len (test_str) - i - 2 )
if col_num > = 1 and col_num < = 16384 :
is_valid = True
print ( "Is string valid excel column : " + str (is_valid))
|
Output
The original string is : C101
Is string valid excel column : True
Time complexity: O(n), where n is the length of the input string.
Auxiliary space: O(1), since we only use a few variables to store the intermediate results.
Last Updated :
02 May, 2023
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...