MS Excel columns have 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. The 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 the 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.
Example:
n = 700
The remainder (n%26) is 24. So we put ‘X’ in the output string and n becomes n/26 which is 26.
Remainder (26%26) is 0. So we put ‘Z’ in the output string and n becomes n/26 -1 which is 0.
Following is the implementation of the above approach.
C++
// C++ program to find Excel // column name from a given // column number #include <bits/stdc++.h> #define MAX 50 using namespace std; // Function to print Excel column name for a given column number void printString( int n) { char str[MAX]; // To store result (Excel column name) int i = 0; // To store current index in str which is result while (n > 0) { // Find remainder int rem = n % 26; // If remainder is 0, then a 'Z' must be there in output if (rem == 0) { str[i++] = 'Z' ; n = (n / 26) - 1; } else // If remainder is non-zero { str[i++] = (rem - 1) + 'A' ; n = n / 26; } } str[i] = '\0' ; // Reverse the string and print result reverse(str, str + strlen (str)); cout << str << endl; return ; } // Driver program to test above function int main() { printString(26); printString(51); printString(52); printString(80); printString(676); printString(702); printString(705); return 0; } |
Java
// Java program to find Excel // column name from a given // column number public class ExcelColumnTitle { // Function to print Excel column // name for a given column number private static void printString( int columnNumber) { // To store result (Excel column name) StringBuilder columnName = new StringBuilder(); while (columnNumber > 0 ) { // Find remainder int rem = columnNumber % 26 ; // If remainder is 0, then a // 'Z' must be there in output if (rem == 0 ) { columnName.append( "Z" ); columnNumber = (columnNumber / 26 ) - 1 ; } else // If remainder is non-zero { columnName.append(( char )((rem - 1 ) + 'A' )); columnNumber = columnNumber / 26 ; } } // Reverse the string and print result System.out.println(columnName.reverse()); } // Driver program to test above function public static void main(String[] args) { printString( 26 ); printString( 51 ); printString( 52 ); printString( 80 ); printString( 676 ); printString( 702 ); printString( 705 ); } } // This code is contributed by Harikrishnan Rajan |
Python
# 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 = [ "\0" ] * 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] = '\0' # 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 |
C#
// C# program to find Excel // column name from a given // column number using System; class GFG{ static String reverse(String input) { char [] reversedString = input.ToCharArray(); Array.Reverse(reversedString); return new String(reversedString); } // Function to print Excel column // name for a given column number private static void printString( int columnNumber) { // To store result (Excel column name) String columnName = "" ; while (columnNumber > 0) { // Find remainder int rem = columnNumber % 26; // If remainder is 0, then a // 'Z' must be there in output if (rem == 0) { columnName += "Z" ; columnNumber = (columnNumber / 26) - 1; } // If remainder is non-zero else { columnName += ( char )((rem - 1) + 'A' ); columnNumber = columnNumber / 26; } } // Reverse the string columnName = reverse(columnName); // Print result Console.WriteLine(columnName.ToString()); } // Driver code public static void Main(String[] args) { printString(26); printString(51); printString(52); printString(80); printString(676); printString(702); printString(705); } } // This code is contributed by amal kumar choubey |
Output
Z AY AZ CB YZ ZZ AAC
Method 2
The problem is similar to converting a decimal number to its binary representation but instead of a 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
Step 1:
Consider we have number 676, How to get its representation in the base 26 system? The same way we do for a binary system, Instead of division and remainder by 2, we do division and remainder by 26.
Base 26 representation of 676 is : 100
Step2
But Hey, we can’t have zero in our representation. Right? Because it’s not part of our number system. How do we get rid of zero? Well its simple, but before doing that let’s remind one simple math trick:
Subtraction: 5000 - 9, How do you subtract 9 from 0 ? You borrow from next significant bit, right.
- In a decimal number system to deal with zero, we borrow 10 and subtract 1 from the next significant.
- In Base 26 Number System to deal with zero, we borrow 26 and subtract 1 from the next significant bit.
So Convert 10026 to a number system which does not have ‘0’, we get (25 26)26
Symbolic representation of the same is : YZ
Here is the implementation of the same:
C++
#include <iostream> using namespace std; void printString( int n) { int arr[10000]; int i = 0; // Step 1: Converting to number assuming // 0 in number system while (n) { arr[i] = n % 26; n = n / 26; i++; } // Step 2: Getting rid of 0, as 0 is // not part of number system for ( int j = 0; j < i - 1; j++) { if (arr[j] <= 0) { arr[j] += 26; arr[j + 1] = arr[j + 1] - 1; } } for ( int j = i; j >= 0; j--) { if (arr[j] > 0) cout << char ( 'A' + arr[j] - 1); } cout << endl; } // Driver program to test above function int main() { printString(26); printString(51); printString(52); printString(80); printString(676); printString(702); printString(705); return 0; } // This code is contributed by Ankur Goel |
Java
import java.util.*; class GFG{ static void printString( int n) { int []arr = new int [ 10000 ]; int i = 0 ; // Step 1: Converting to number // assuming 0 in number system while (n > 0 ) { arr[i] = n % 26 ; n = n / 26 ; i++; } // Step 2: Getting rid of 0, as 0 is // not part of number system for ( int j = 0 ; j < i - 1 ; j++) { if (arr[j] <= 0 ) { arr[j] += 26 ; arr[j + 1 ] = arr[j + 1 ] - 1 ; } } for ( int j = i; j >= 0 ; j--) { if (arr[j] > 0 ) System.out.print( ( char )( 'A' + arr[j] - 1 )); } System.out.println(); } // Driver code public static void main(String[] args) { printString( 26 ); printString( 51 ); printString( 52 ); printString( 80 ); printString( 676 ); printString( 702 ); printString( 705 ); } } // This code is contributed by amal kumar choubey |
Python3
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 |
C#
using System; class GFG{ static void printString( int n) { int []arr = new int [10000]; int i = 0; // Step 1: Converting to // number assuming 0 in // number system while (n > 0) { arr[i] = n % 26; n = n / 26; i++; } // Step 2: Getting rid of 0, // as 0 is not part of number // system for ( int j = 0; j < i - 1; j++) { if (arr[j] <= 0) { arr[j] += 26; arr[j + 1] = arr[j + 1] - 1; } } for ( int j = i; j >= 0; j--) { if (arr[j] > 0) Console.Write(( char )( 'A' + arr[j] - 1)); } Console.WriteLine(); } // Driver code public static void Main(String[] args) { printString(26); printString(51); printString(52); printString(80); printString(676); printString(702); printString(705); } } // This code is contributed by 29AjayKumar |
Output:
Z AY AZ CB YZ ZZ AAC
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.