Open In App

Find Excel column name from a given column number

Improve
Improve
Like Article
Like
Save
Share
Report

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 “A”, column 2 as “B”, and 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 the 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 actuality 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


Javascript




<script>
// Javascript program to find Excel
// column name from a given
// column number
 
// Function to print Excel column
    // name for a given column number
function printString(columnNumber)
{
    // To store result (Excel column name)
        let columnName = [];
  
        while (columnNumber > 0) {
            // Find remainder
            let rem = columnNumber % 26;
  
            // If remainder is 0, then a
            // 'Z' must be there in output
            if (rem == 0) {
                columnName.push("Z");
                columnNumber = Math.floor(columnNumber / 26) - 1;
            }
            else // If remainder is non-zero
            {
                columnName.push(String.fromCharCode((rem - 1) + 'A'.charCodeAt(0)));
                columnNumber = Math.floor(columnNumber / 26);
            }
        }
  
        // Reverse the string and print result
        document.write(columnName.reverse().join("")+"<br>");
}
 
// Driver program to test above function
printString(26);
printString(51);
printString(52);
printString(80);
printString(676);
printString(702);
printString(705);
 
 
// This code is contributed by rag2127
</script>


Output

Z
AY
AZ
CB
YZ
ZZ
AAC

Time Complexity: O(log26n), as we are using a loop and in each traversal, we decrement by floor division of 26.

Auxiliary Space: O(50), as we are using extra space for storing the result.
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:

  1. Convert the number to base 26 representation, considering we have 0 also in the system.
  2. 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? In 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 it’s 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 the 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 that 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


Javascript




<script>
 
function printString(n){
    let arr = [];
    let i = 0;
 
    // Step 1: Converting to number assuming
    // 0 in number system
    while (n) {
        arr[i] = n % 26;
        n = Math.floor(n / 26);
        i++;
    }
 
    // Step 2: Getting rid of 0, as 0 is
    // not part of number system
    for (let j = 0; j < i - 1; j++) {
        if (arr[j] <= 0) {
            arr[j] += 26;
            arr[j + 1] = arr[j + 1] - 1;
        }
    }
    let ans = '';
    for (let j = i; j >= 0; j--) {
        if (arr[j] > 0)
            ans += String.fromCharCode(65 + arr[j] - 1);
    }
 
    document.write(ans + "<br>");
}
 
// Driver program to test above function
 
printString(26);
printString(51);
printString(52);
printString(80);
printString(676);
printString(702);
printString(705);
 
</script>


Output

Z
AY
AZ
CB
YZ
ZZ
AAC

Time Complexity: O(log26n), as we are using a loop and in each traversal, we decrement by floor division of 26.

Auxiliary Space: O(10000), as we are using extra space for the array.

Method 3:

We can use a recursive function which definitely reduces the time and  increase the efficiency:

Alphabets are in sequential order like: ‘ABCDEFGHIJKLMNOPQRSTUVWXYZ’. You have experienced while using excel when you see columns and rows numbering are done in  Alphabetical ways.

Here’s How I purposefully think about the logic of how it is arranged.

(In Mathematical  terms, [a , b ] means from ‘a’ to ‘b’).

[1,26] = [A,Z] (Understand by ‘1’ stands for ‘A’ and ’26” stands for “Z”). For [27,52] ,it will be like [AA,AZ], For [57,78] it will be [BA,BZ]

Logic is to append an Alphabet sequentially whenever it ends up numbering at 26.

For example, if the number is ’27’ which is greater than  ’26’, then we simply need to divide by 26, and we get the remainder as 1, We see “1” as “A” and can be recursively done.

we will be using python for this.

Algorithm is:

1. Take an array and Sort the letters from A to Z . (You can also use the import string and string function to get “A to Z” in uppercase.)

2. If the number is less than or equal to ’26’, simply get the letter from the array and print it.

3. If it is greater than 26, use the Quotient  Remainder rule, if the remainder is zero, there are 2 possible ways, if the quotient is “1”, simply hash out the letter from the index [r-1]( ‘r’ is remainder), else call out the function from the num =(q-1) and append at the front to the letter indexing [r-1].

4. If the remainder is not equal to “0”, call the function for the num = (q) and append at the front to the letter indexing [r-1].

The code concerned with this is:

C++




#include<bits/stdc++.h>
using namespace std;
 
// Or you can simply take a string and perform this logic ,no issue i found in here.
string 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
string num_hash(int num){
    if(num < 26){
      string res = "";
      res += alpha[num-1];
      return res;
    }
    else{
      int q = (num / 26);
      int r = num % 26;
      string res = "";
      if(r == 0){
        if(q == 1){
          res.append(1,alpha[(26 + r-1)%26]);
        }
        else{
          res = num_hash(q-1);
          res.append(1,alpha[(26 + r-1)%26]);
        }
      }
      else{
        res = num_hash(q);
        res.append(1,alpha[(26 + r-1)%26]);
      }
      return res;
    }
}
 
int main () {
 
    cout<< num_hash(26) << endl;
    cout<< num_hash(51) << endl;
    cout<< num_hash(52) << endl;
    cout<< num_hash(80) << endl;
    cout<< num_hash(676) << endl;
    cout<< num_hash(702) << endl;
    cout<< num_hash(705) << endl;
 
    return 0;
 
}
 
// This code is contributed by shinjanpatra


Java




/*package whatever //do not write package name here */
import java.io.*;
class GFG
{
 
  // Or you can simply take a string and perform this logic ,no issue i found in here.
  static String 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
  static String num_hash(int num){
    if(num < 26)
      return Character.toString(alpha.charAt(num-1));
    else{
      int q = Math.floorDiv(num, 26);
      int r = num % 26;
      if(r == 0){
        if(q == 1){
          return Character.toString(alpha.charAt((26 + r-1)%26));
        }
        else
          return num_hash(q-1) + alpha.charAt((26 + r-1)%26);
      }
      else
        return num_hash(q) + alpha.charAt((26 + r-1)%26);
    }
  }
 
  public static void main (String[] args) {
 
    System.out.println(num_hash(26));
    System.out.println(num_hash(51));
    System.out.println(num_hash(52));
    System.out.println(num_hash(80));
    System.out.println(num_hash(676));
    System.out.println(num_hash(702));
    System.out.println(num_hash(705));
 
  }
}
 
// This code is contributed by shinjanpatra


Python3




# 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))


C#




// C# code for above approach
using System;
class GFG
{
 
// Or you can simply take a string and perform this logic ,no issue i found in here.
static string 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
static string num_hash(int num){
    if(num < 26)
    return Char.ToString(alpha[num-1]);
    else{
    int q = num/26;
    int r = num % 26;
    if(r == 0){
        if(q == 1){
        return Char.ToString(alpha[(26 + r-1)%26]);
        }
        else
        return num_hash(q-1) + alpha[(26 + r-1)%26];
    }
    else
        return num_hash(q) + alpha[(26 + r-1)%26];
    }
}
 
public static void Main(String[] args) {
 
    Console.WriteLine(num_hash(26));
    Console.WriteLine(num_hash(51));
    Console.WriteLine(num_hash(52));
    Console.WriteLine(num_hash(80));
    Console.WriteLine(num_hash(676));
    Console.WriteLine(num_hash(702));
    Console.WriteLine(num_hash(705));
 
}
}
 
// This code is contributed by Aman Kumar


Javascript




<script>
 
// Or you can simply take a string and perform this logic ,no issue i found in here.
let 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
function num_hash(num)
{
    if(num < 26)
        return alpha[num-1]
    else{
        let q = Math.floor(num/26),r = num % 26
        if(r == 0){
            if(q == 1)
                return alpha[(26 + r-1)]
            else
                return num_hash(q-1) + alpha[(26 + 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
document.write(num_hash(26),"</br>")
document.write(num_hash(51),"</br>")
document.write(num_hash(52),"</br>")
document.write(num_hash(80),"</br>")
document.write(num_hash(676),"</br>")
document.write(num_hash(702),"</br>")
document.write(num_hash(705),"</br>")
 
// This code is contributed by shinjanpatra
 
</script>


Output

Z
AY
AZ
CB
YZ
ZZ
AAC

Time Complexity: O(log26n), as we are using recursion and in each recursive call, we decrement by floor division of 26.

Auxiliary Space: O(1), as we are not using any extra space.
Related Article : 
Find the Excel column number from the column title



Last Updated : 17 Aug, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads