Convert column name in Excel to the corresponding number
Given a string representing the column name in an Excel sheet, return the number corresponding to the Excel column name.
For example, the following image shows numbers corresponding to Excel columns:
To better understand the solution, let’s first consider a simple mathematical relation. A number can be broken down into the sum of its digits multiplied by basen, where n is the number of positions the digit is away from the one’s place. For example, a decimal number 125 (having a base 10) can be written as (1 × 10 × 10) + (2 × 10) + 5.
Excel maps column names using the base number 26. If we apply the same logic to it, the excel column "ALH" can be broken down into (1 × 26 × 26) + (12 × 26) + 8, since A=1, L=12, and H=8. Programmatically, it can be done iteratively using a loop.
AL = A × 26 + 12 = 1 × 26 + 12 = 38
ALH = AL × 26 + 8 = 38 × 26 + 8 = 996
Following is the C++, Java, and Python implementation based on the idea:
C++
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
#include <iostream> #include <algorithm> #include <string> using namespace std; // Function to convert an excel sheet's column name to the corresponding number int getValue(string str) { // base case: empty string if (str.empty()) { return 0; } // convert the input string to uppercase for_each(str.begin(), str.end(), [](char &ch) { ch = ::toupper(static_cast<unsigned char>(ch)); }); int value = 0; for (char ch: str) { value = value * 26 + (ch - 'A' + 1); } return value; } int main() { string str = "ALH"; cout << getValue(str); return 0; } |
Output:
996
Java
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
class Main { // Function to convert an excel sheet's column name to the corresponding number public static int getValue(String str) { // base case if (str == null || str.isEmpty()) { return 0; } int value = 0; // iterate over the input string after converting it to uppercase for (char ch: str.toUpperCase().toCharArray()) { value = value * 26 + (ch - 'A' + 1); } return value; } public static void main(String[] args) { String str = "ALH"; System.out.println(getValue(str)); } } |
Output:
996
Python
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
# Function to convert an excel sheet's column name to the corresponding number def getValue(s): # base case if not s: return 0 value = 0 # iterate over the input string after converting it to uppercase for ch in s: value = value * 26 + (ord(ch) - ord('A') + 1) return value if __name__ == '__main__': s = 'ALH' print(getValue(s)) |
Output:
996
Also See:
Thanks for reading.
To share your code in the comments, please use our online compiler that supports C, C++, Java, Python, JavaScript, C#, PHP, and many more popular programming languages.
Like us? Refer us to your friends and support our growth. Happy coding :)