Find Excel column name from a given column number

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.

Example:
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.

C++

filter_none

edit
close

play_arrow

link
brightness_4
code

// 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;
}

chevron_right






                        filter_none









Java














                                    filter_none




                                    edit

                                    close




                                    play_arrow




                                    link

                                    brightness_4

                                    code
                                




















// 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 



















                        chevron_right







                        filter_none









Python














                                    filter_none




                                    edit

                                    close




                                    play_arrow




                                    link

                                    brightness_4

                                    code
                                




















# 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 



















                        chevron_right







                        filter_none










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





    

        My Personal Notes
        arrow_drop_up
    

    

        
        

            
            
        

    


Recommended Posts: