MS Excel columns has a pattern like A, B, C, …, Z, AA, AB, AC, …., AZ, BA, BB, … ZZ, AAA, AAB ….. etc. In other words, column 1 is named as “A”, column 2 as “B”, column 27 as “AA”.
Given a column number, find its corresponding Excel column name. Following are more examples.
Input Output 26 Z 51 AY 52 AZ 80 CB 676 YZ 702 ZZ 705 AAC
Thanks to Mrigank Dembla for suggesting the below solution in a comment.
Suppose we have a number n, let’s say 28. so corresponding to it we need to print the column name. We need to take remainder with 26.
If remainder with 26 comes out to be 0 (meaning 26, 52 and so on) then we put ‘Z’ in the output string and new n becomes n/26 -1 because here we are considering 26 to be ‘Z’ while in actual it’s 25th with respect to ‘A’.
Similarly if the remainder comes out to be non zero. (like 1, 2, 3 and so on) then we need to just insert the char accordingly in the string and do n = n/26.
Finally we reverse the string and print.
n = 700
Remainder (n%26) is 24. So we put ‘X’ in output string and n becomes n/26 which is 26.
Remainder (26%26) is 0. So we put ‘Z’ in output string and n becomes n/26 -1 which is 0.
Following is C++ implementation of above approach.
Z AY AZ CB YZ ZZ AAC
The problem is similar to converting a decimal number to its binary representation but instead of binary base system where we have two digits only 0 and 1, here we have 26 characters from A-Z .
So, we are dealing with base 26 instead of base binary.
That’s not where the fun ends, we don’t have zero in this number system, as A represents 1, B represents 2 and so on Z represents 26.
To make the problem easily understandable, we approach the problem in two steps:
- Convert the number to base 26 representation, considering we have 0 also in the system.
- Change the representation to the one without having 0 in its system.
HOW? Here is an example
Consider we have number 676, How to get its representation in base 26 system ? Same way we do for binary system, Instead of division and remainder by 2, we do division and remainder by 26.
Base 26 representation of 676 is : 100
But Hey, we can’t have zero in our representation. right? Because its not part of our number system. How do we get rid of zero? Well its simple, but before doing that lets remind one simple math trick:
Subtraction: 5000 - 9, How do you subtract 9 from 0 ? You borrow from next significant bit, right.
- In decimal number system to deal with zero we borrow 10, and subtract 1 from next significant.
- In Base 26 Number System to deal with zero we borrow 26 and subtract 1 from next significant bit.
So Convert 10026 to number system which does not have ‘0’, we get (25 26)26
Symbolic representation of the same is : YZ
Here is the C++ Implementation of the same:
Related Article :
Find Excel column number from column title
This article is contributed by Kartik. Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above
Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.
- Find Excel column number from column title
- Maximum sum of a Matrix where each value is from a unique row and column
- Print an N x M matrix such that each row and column has all the vowels in it
- Removing row or column wise duplicates from matrix of characters
- Print all elements in sorted order from row and column wise sorted matrix
- Count Negative Numbers in a Column-Wise and Row-Wise Sorted Matrix
- Check if a grid can become row-wise and column-wise sorted after adjacent swaps
- Kth smallest element in a row-wise and column-wise sorted 2D array | Set 1
- Search in a row wise and column wise sorted matrix
- Given a number as a string, find the number of contiguous subsequences which recursively add up to 9 | Set 2
- Given a number as a string, find the number of contiguous subsequences which recursively add up to 9
- Find the smallest number whose digits multiply to a given number n
- Find the number of integers x in range (1,N) for which x and x+1 have same number of divisors
- Find the maximum number of composite summands of a number
- Find the Number Occurring Odd Number of Times
- Find number of subarrays with even sum
- Find the Nth Mosaic number
- Find the Number which contain the digit d
- Find nth Hermite number
- Find first and last digits of a number