# [ACCEPTED]-How to convert Excel sheet column names into numbers?-excel

Score: 64

This function should work for an arbitrary 1 length column name.

``````public static int GetColumnNumber(string name)
{
int number = 0;
int pow = 1;
for (int i = name.Length - 1; i >= 0; i--)
{
number += (name[i] - 'A' + 1) * pow;
pow *= 26;
}

return number;
}
``````
Score: 17

I had to deal with this a few months ago. The 8 inverse - column index to column name - is 7 fun, too, and becomes really messy if you 6 try to solve it with a zero based index 5 not recognizing that this complicates things. It 4 could be so simple if it would be a normal 3 polyadic numeral system ...

Here is a simplified 2 version of my solution as a extension method 1 without error handling and all that stuff.

``````public static Int32 ToOneBasedIndex(this String name)
{
return name.ToUpper().
Aggregate(0, (column, letter) => 26 * column + letter - 'A' + 1);
}
``````
Score: 3

I've been working with this for a while 12 now and found this to work really good for 11 columns that go beyond A-Z, or even beyond 10 AA-ZZ... It's accomplished by breaking apart 9 each character in the string and recursively 8 calling itself to derive the DEC value of 7 the ASCII character (less 64), then multiplying 6 it by 26^n. A return value of long was used 5 to overcome a potential limitation when 4 n > 4.

``````    public long columnNumber(String columnName)
{
char[] chars = columnName.ToUpper().ToCharArray();

return (long)(Math.Pow(26, chars.Count() - 1)) *
(System.Convert.ToInt32(chars[0]) - 64) +
((chars.Count() > 2) ? columnNumber(columnName.Substring(1, columnName.Length - 1)) :
((chars.Count() == 2) ? (System.Convert.ToInt32(chars[chars.Count() - 1]) - 64) : 0));
}
``````

Also, if you'd like to get the inverse 3 (i.e. pass in the columnNumber and get the 2 columnName, here's some code that works 1 for that.

``````    public String columnName(long columnNumber)
{
StringBuilder retVal = new StringBuilder();
int x = 0;

for (int n = (int)(Math.Log(25*(columnNumber + 1))/Math.Log(26)) - 1; n >= 0; n--)
{
x = (int)((Math.Pow(26,(n + 1)) - 1) / 25 - 1);
if (columnNumber > x)
retVal.Append(System.Convert.ToChar((int)(((columnNumber - x - 1) / Math.Pow(26, n)) % 26 + 65)));
}

return retVal.ToString();
}
``````
Score: 1

Source code:

``````namespace XLS
{
/// <summary>
/// Represents a single cell in a excell sheet
/// </summary>
public struct Cell
{
private long row;
private long column;
private bool dataChange;

/// <summary>
/// Initializes a new instance of the XLS.Cell
/// class with the specified row and column of excel worksheet
/// </summary>
/// <param name="row">The row index of a cell</param>
/// <param name="column">The column index of a cell</param>
public Cell(long row, long column)
{
this.row = row;
this.column = column;
dataChange = true;
}

/// <summary>
/// Initializes a new instance of the XLS.Cell
/// class with the specified address of excel worksheet
/// </summary>
{
dataChange = false;
}

/// <summary>
/// Gets or sets the row of this XLS.Cell
/// </summary>
public long Row
{
get { return row <= 0 ? 1 : row; }
set { row = value; dataChange = true; }
}

/// <summary>
/// Gets or sets the column of this XLS.Cell
/// </summary>
public long Column
{
get { return column <= 0 ? 1 : column; }
set { column = value; dataChange = true; }
}

/// <summary>
/// Gets or sets the address of this XLS.Cell
/// </summary>
{
set
{
}
}

/// <summary>
/// Gets the column address of this XLS.Cell
/// </summary>
{
private set { columnAddress = value; }
}

#region Private Methods

{
}

{
}

{
int sum = 0;
for (int i = 0; i < characters.Length; i++)
{
sum *= 26;
sum += (characters[i] - 'A' + 1);
}

return (long)sum;
}

{
}

{
string indexToString = string.Empty;

if (Column > 26)
{
indexToString = ((char)(65 + (int)((Column - 1) / 26) - 1)).ToString();
}

indexToString += (char)(65 + ((Column - 1) % 26));

dataChange = false;
return indexToString + Row;
}

#endregion
}
``````

}

0

Score: 0

O24 has a column number and you want a name:

O37 1 has a column name and you want a number:

=COLUMN(INDIRECT(O37&1))

Score: 0
``````public static string GetColumnName(int index)
{
const string letters = "ZABCDEFGHIJKLMNOPQRSTUVWXY";

int NextPos = (index / 26);
int LastPos = (index % 26);
if (LastPos == 0) NextPos--;

if (index > 26)
return GetColumnName(NextPos) + letters[LastPos];
else
return letters[LastPos] + "";
}
``````

0

More Related questions