Convert A to 1 B to 2 ... Z to 26 and then AA to 27 AB to 28 (column indexes to column references in Excel)

given a column title as appears in an excel spreadsheet, return its corresponding column number.
how to convert excel column name to number
convert number to excel column letter
convert number to alphabet c#
write a function that converts a character to its index in the alphabet eg a 1 b 2 c 3 z 26
how to get numeric position of alphabets in c#
c# abc to 123
excel column number

Does any one have algorithm or logic to Convert A to 1 ,B to 2, ... ,Z to 26 and then ,AA to 27, AB to 28 etc.

In other words, converting a column index into the column reference in Excel.

Have a look at these

/// <summary>
/// 1 -> A<br/>
/// 2 -> B<br/>
/// 3 -> C<br/>
/// ...
/// </summary>
/// <param name="column"></param>
/// <returns></returns>
public static string ExcelColumnFromNumber(int column)
{
    string columnString = "";
    decimal columnNumber = column;
    while (columnNumber > 0)
    {
        decimal currentLetterNumber = (columnNumber - 1) % 26;
        char currentLetter = (char)(currentLetterNumber + 65);
        columnString = currentLetter + columnString;
        columnNumber = (columnNumber - (currentLetterNumber + 1)) / 26;
    }
    return columnString;
}

/// <summary>
/// A -> 1<br/>
/// B -> 2<br/>
/// C -> 3<br/>
/// ...
/// </summary>
/// <param name="column"></param>
/// <returns></returns>
public static int NumberFromExcelColumn(string column)
{
    int retVal = 0;
    string col = column.ToUpper();
    for (int iChar = col.Length - 1; iChar >= 0; iChar--)
    {
        char colPiece = col[iChar];
        int colNum = colPiece - 64;
        retVal = retVal + colNum * (int)Math.Pow(26, col.Length - (iChar + 1));
    }
    return retVal;
}

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, … In other words, column 1 is named as “A”, column 2 as “B”, column 27 as “AA”. let's say 28. so corresponding to it we need to print the column name. 26, 52 and so on) then we put 'Z' in the output string and new n becomes n/26 -1  Given a column title as appear in an Excel sheet, return its corresponding column number. For example: A - 1 B - 2 C - 3 Z - 26 AA - 27 AB - 28. The O (n) solution checks for each character of the string. For example, ABC = A * 26 * 26 + B * 26 + C. The left-most digit will be multiplied by 26 each iteration when a new digit is met.

Here's a simple LINQ expression:

static int TextToNumber(this string text) {
    return text
        .Select(c => c - 'A' + 1)
        .Aggregate((sum, next) => sum*26 + next);
}

This test

Console.WriteLine(" A -> " + "A".TextToNumber());
Console.WriteLine(" B -> " + "B".TextToNumber());
Console.WriteLine(" Z -> " + "Z".TextToNumber());
Console.WriteLine("AA -> " + "AA".TextToNumber());
Console.WriteLine("AB -> " + "AB".TextToNumber());

will produce this output:

 A -> 1
 B -> 2
 Z -> 26
AA -> 27
AB -> 28

Update: Here's the same code but targetting .NET 2.0:

static int TextToNumber(string text) {
    int sum = 0;
    foreach (char c in text) {
        sum = sum*26 + c - 'A' + 1;
    }
    return sum;
}

Convert number into corresponding excel column name, For example, below image shows numbers corresponding to excel columns. problem lies in the handling the boundary cases like number 26 corresponds to '​Z' and 27 corresponds to 'AA'. 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. 23. 24 find index of next letter and concatenate the letter. A = 1, B = 2, …, Z = 26, AZ = 27, …, XFD = 16,384. This example converts between these Excel-style column numbers and names. It’s a little trickier than you’d think. When you type a column name, the program converts it into a number and then converts that number back into a name so you can see that the two conversions work.

This is a code for JavaScript if you prefer it done on the client side

<script type="text/javascript" lang="javascript">
function Alphabet2Numeric(mystr) {
    mystr = mystr.toUpperCase(); //Hence the ASCII code 64 down there
    var sum = 0;
    for (var i = 0; i < mystr.length; i++) {
        sum = sum * 26 + mystr.charCodeAt(i) - 64; //returns 1 for 'a' and 2 for 'b' so on and so forth.
    }
    return sum;
}
</script>

Conversion from integer to Excel Column (Base 26) A-Z/AA/AAA etc , Conversion from integer to Excel Column (Base 26) A-Z/AA/AAA etc but this only works until column Z. How do I modify this so that it works for column AA, BA, AAA etc? 1 Like. For each row Activity에서 열 인덱스 문제. How to get excel Excel contains any data or you want just end cell reference of an empty excel? Excel Column Title: Given a positive integer, return its corresponding column title as appear in an Excel sheet. For example: 1 -> A 2 -> B 3 -> C 26 -> Z 27 -> AA 28 -> AB Toggle navigation

Same problem, different language: PHP.

function charToInt($char)
{
   $array = array_flip(range(a, z));
   return $array[$char] + 1;
}

echo charToInt('c');

outputs: 3

Alphabetic Column Designation (Microsoft Excel), When COLUMN returns a value for columns A through Z (1 through 26), this can be added to 64 to get the letters of those columns, 65 through 90. 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’.

Excel Column Letter to Number Quick Reference, a column letter? Here is a list with Excel columns A to ZA ranging from column 1 to 702. A, 1. B, 2. C, 3. D, 4. E, 5. F, 6. G, 7. H, 8. I, 9. J, 10. K, 11. L, 12. M, 13. N, 14. O, 15. P, 16. Q, 17. R, 18. S, 19. T, 20. U, 21. V, 22. W, 23. X, 24. Y, 25. Z, 26 AA, 27. AB, 28. AC, 29. AD, 30. AE, 31. AF, 32. AG, 33. AH, 34. AI, 35. AJ, 36​. After reading this, I decided to find a way to do it directly in Excel cells. It even accounts for columns after Z. Just paste this formula into a cell of any row of any column and it will give you the corresponding number.

range - Manual, Change language: User Contributed Notes 28 notes If one is numeric, including numeric string, then the other is treated as numeric; if it is a non-​numeric string, it is treated as This could be used for example to get the excel column indexes. e.g. A=1 - B=2 - Z=26 - AA=27 - CZ=104 - DA=105 - ZZ=702 - AAA=703 */ I'm looking for the opposite to this Q&A: Convert an excel or spreadsheet column letter to its number in Pythonic fashion. or this one but in python How to convert a column number (eg. 127) in

A Tutorial Introduction to R, 9.2.1 Creating matrices; 9.2.2 cbind and rbind; 9.2.3 Matrix indexing 15 Vectorized functions vs loops; 16 Acknowledgments; References v <- z[c(1,2,5​)]; v You may be wondering if vectors in R are row vectors or column vectors (if you 21 a ## 22 22 b ## 23 23 c ## 24 24 d ## 25 25 e ## 26 26 f ## 27 27 g ## 28 28  Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Comments
  • Is there any reason why you are using decimal as type for columnNumber and currentLetterNumber?
  • No, this is just the way I originally implemented it. You can change it to ints. Tested quick and it seems fine.
  • +1 for using a fold. You don't even need to convert the string to a char array, since a string behaves like an IEnumerable<char> already. E.g. s.Select(c => c - 'A' + 1).Aggregate((sum, next) => sum * 26 + next)
  • Thx cfern. I forgot that string implements IEnumerable<char>. I've updated my answer
  • Neat solution but Linq is not supported in visual studio 2005
  • Brah... you need to ToCharArray() that string in 3.5 b4 you throw down a Select
  • @Merritt I guess it should be sufficient to include the System.Linq namespace