Write a program to find Excel column labels by a given number of columns in Python

Microsoft Excel columns are labeled with the style A, B, C,., Z, AA, AB, AC,., AZ, BA, BB, . ZZ, AAA, AAB. Say alternatively, column 1 will be labeled A, column 2 will be labeled B, and column 27 will be labeled AA.

Problem : Given the number of columns, find the corresponding column label in Excel.

For example:

Input Output 26 Z 51 AY 52 AZ 80 CB 676 YZ 702 ZZ 705 AAC

In this article, TipsMake.com will learn with you how to write a program to find Excel column labels by a given number of columns in Python.

Method 1:

Suppose we have a number n, let's say n = 28. We need to find the column label in Excel that corresponds to n. We need to consider the remainder of the operation n divided by 26.

If n is divisible by 26 and the remainder is 0 (meaning n is 26, 52,.) then we put Z in the output string and n becomes n/26-1 because here we are 26 is Z when in fact it is 25th for A.

Similarly, if the remainder is non-zero (eg 1, 2, 3,.) then we just insert the matching character into the string and do n = n/26.

Finally, we reverse the string and return the result.

For example:

n = 700 Số dư của phép toán (n%26) là 24. Do đó, chúng ta đưa X vào trong chuỗi output và n trở thành n/26 với kết quả là 26. Số dư của phép toán 26%26 là 0. Do đó, chúng ta đưa Z vào trong chuỗi output và n trở thành n/26-1 với kết quả là 0.

Here is the sample code for your reference:

# Python program to find Excel column name from a # given column number MAX = 50 # Function to print Excel column name # for a given column number def printString(n): # To store result (Excel column name) string = ["�"] * MAX # To store current index in str which is result i = 0 while n > 0: # Find remainder rem = n % 26 # if remainder is 0, then a # 'Z' must be there in output if rem == 0: string[i] = 'Z' i += 1 n = (n / 26) - 1 else: string[i] = chr((rem - 1) + ord('A')) i += 1 n = n / 26 string[i] = '�' # Reverse the string and print result string = string[::-1] print "".join(string) # Driver program to test the above Function printString(26) printString(51) printString(52) printString(80) printString(676) printString(702) printString(705) # This code is contributed by BHAVYA JAIN

The returned result is:

Z AY AZ CB YZ ZZ AAC

Method 2:

This problem is similar to the problem of converting decimal to binary, but instead of binary with only 2 numbers 0 and 1, here we have 26 characters from A to Z. Therefore, they We will perform the conversion problem with a factor of 26 instead of a binary factor.

This is an interesting problem because we won't have zero in this number system because A represents 1, B represents 2 and finally Z represents 26.

To make the problem easier to grasp, we will approach it in two steps:

1. Convert the number to a factor of 26 assuming 0 is still present in the system.

2. Change the factor to a zero-zero system.

Here is an example:

Step 1:

Let's say we have the number 676, how to convert it to a factor of 26? We still do it the same way we do with the binary system, instead of dividing by 2 and calculating the remainder, we do the division by 26 and calculate the remainder.

Số 676 trong hệ số 26 là 100 

Step 2:

But we don't have zero in our base system. How do we remove the zero?

1. In the decimal number system, to process zero, we borrow 10 and subtract 1 from the next significant number.
2. In a system with number 26, to deal with zero, we borrow 26 and subtract 1 from the next significant number.

Thus, to convert 100 in a factor of 26 to a factor of 26 but without a zero, we get the number (25 26).

The character representation of this number is: YZ.

Here is the sample code for your reference:

def printString(n): arr = [0] * 10000 i = 0 # Step 1: Converting to number # assuming 0 in number system while (n > 0): arr[i] = n % 26 n = int(n // 26) i += 1 #Step 2: Getting rid of 0, as 0 is # not part of number system for j in range(0, i - 1): if (arr[j] <= 0): arr[j] += 26 arr[j + 1] = arr[j + 1] - 1 for j in range(i, -1, -1): if (arr[j] > 0): print(chr(ord('A') + (arr[j] - 1)), end = ""); print(); # Driver code if __name__ == '__main__': printString(26); printString(51); printString(52); printString(80); printString(676); printString(702); printString(705); # This code is contributed by Princi Singh

The returned result is:

Z AY AZ CB YZ ZZ AAC

Method 3:

We can use a recursive function to solve this problem more quickly and efficiently.

The alphabets are ordered as "ABCDEFGHIJKLMNOPQRSTUVWXYZ" and the Excel column labeling you already know above.

Therefore, we can apply the following logic to find the answer.

In mathematical terms [a,b] means from a to b. [1,26] = [A,Z] is interpreted as A representing A and Z representing 26. With [27,52] will be equal to [AA,AZ] and [57.78] will be equal to [ BA, BZ].

The logic is that we will append an Alphabet in the correct order every time it ends with 26.

Example: If n is 27 then we just divide by 26 and we get remainder 1. We see 1 is A and can be done recursively.

The specific algorithm is:

1. Take an array and sort the letters from A to Z.

2. If the number n is less than or equal to 26, just get the corresponding letter from the array and return it.

3. If it is greater than 26, use the Rule of Residual Quotient, if remainder is 0, there are 2 possible ways, if quotient is 1, just hash the letter from index [r-1] ( r is the remainder), otherwise call the function from num = (q-1) and prepend the letter index [r-1].

4. If the remainder is non-zero, call the function for num = (q) and prepend the letter index [r-1].

Here is the sample code for your reference:

# Or you can simply take a string and perform this logic ,no issue i found in here. alpha = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' # defined a recursive function here. # if number is less than "26", simply hash out (index-1) # There are sub possibilities in possibilities, # 1.if remainder is zero(if quotient is 1 or not 1) and # 2. if remainder is not zero def num_hash(num): if num < 26: return alpha[num-1] else: q, r = num//26, num % 26 if r == 0: if q == 1: return alpha[r-1] else: return num_hash(q-1) + alpha[r-1] else: return num_hash(q) + alpha[r-1] # Calling the function out here and printing the ALphabets # This code is robust ,work for any positive integer only. # You can try as much as you want print(num_hash(26)) print(num_hash(51)) print(num_hash(52)) print(num_hash(80)) print(num_hash(676)) print(num_hash(702)) print(num_hash(705))

The returned result is:

Z AY AZ CB YZ ZZ AAC

TipsMake.com hope this article will be useful to you.

David Pac

Update 07 July 2023