Convert column index into corresponding column letter
I wrote these a while back for various purposes (will return the double-letter column names for column numbers > 26):
function columnToLetter(column)
{
var temp, letter = '';
while (column > 0)
{
temp = (column - 1) % 26;
letter = String.fromCharCode(temp + 65) + letter;
column = (column - temp - 1) / 26;
}
return letter;
}
function letterToColumn(letter)
{
var column = 0, length = letter.length;
for (var i = 0; i < length; i++)
{
column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
}
return column;
}
Convert spreadsheet number to column letter
start_index = 1 # it can start either at 0 or at 1
letter = ''
while column_int > 25 + start_index:
letter += chr(65 + int((column_int-start_index)/26) - 1)
column_int = column_int - (int((column_int-start_index)/26))*26
letter += chr(65 - start_index + (int(column_int)))
Function to convert column number to letter?
This function returns the column letter for a given column number.
Function Col_Letter(lngCol As Long) As String
Dim vArr
vArr = Split(Cells(1, lngCol).Address(True, False), "$")
Col_Letter = vArr(0)
End Function
testing code for column 100
Sub Test()
MsgBox Col_Letter(100)
End Sub
Convert an excel or spreadsheet column letter to its number in Pythonic fashion
There is a way to make it more pythonic (works with three or more letters and uses less magic numbers):
def col2num(col):
num = 0
for c in col:
if c in string.ascii_letters:
num = num * 26 + (ord(c.upper()) - ord('A')) + 1
return num
And as a one-liner using reduce (does not check input and is less readable so I don't recommend it):
col2num = lambda col: reduce(lambda x, y: x*26 + y, [ord(c.upper()) - ord('A') + 1 for c in col])
How to convert a column number (e.g. 127) into an Excel column (e.g. AA)
Here's how I do it:
private string GetExcelColumnName(int columnNumber)
{
string columnName = "";
while (columnNumber > 0)
{
int modulo = (columnNumber - 1) % 26;
columnName = Convert.ToChar('A' + modulo) + columnName;
columnNumber = (columnNumber - modulo) / 26;
}
return columnName;
}
Translate a column index into an Excel Column Name
The answer I came up with is to get a little recursive. This code is in VB.Net:
Function ColumnName(ByVal index As Integer) As String
Static chars() As Char = {"A"c, "B"c, "C"c, "D"c, "E"c, "F"c, "G"c, "H"c, "I"c, "J"c, "K"c, "L"c, "M"c, "N"c, "O"c, "P"c, "Q"c, "R"c, "S"c, "T"c, "U"c, "V"c, "W"c, "X"c, "Y"c, "Z"c}
index -= 1 ' adjust so it matches 0-indexed array rather than 1-indexed column
Dim quotient As Integer = index \ 26 ' normal / operator rounds. \ does integer division, which truncates
If quotient > 0 Then
ColumnName = ColumnName(quotient) & chars(index Mod 26)
Else
ColumnName = chars(index Mod 26)
End If
End Function
And in C#:
string ColumnName(int index)
{
index -= 1; //adjust so it matches 0-indexed array rather than 1-indexed column
int quotient = index / 26;
if (quotient > 0)
return ColumnName(quotient) + chars[index % 26].ToString();
else
return chars[index % 26].ToString();
}
private char[] chars = new char[] {'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'};
The only downside it that it uses 1-indexed columns rather than 0-indexed.
Convert Column To Field Order
You are really close. You are going to want to use the ASC() function which returns the ASCII value of a character. When you subtract 64, it will get you the correct column index.
Sub Test()
Dim colletter As String
colletter = "C"
Debug.Print Asc(colletter) - 64
End Sub
*EDIT: I've added some controls for multiple letters and to make sure that the letters are upper case. This does, however, limit it to only having two letters, meaning column "ZZ" is your last column, but hopefully your user doesn't have more than 702 columns. :)
Sub Test()
Dim colLetter As String
Dim colNumber As Integer
Dim multiplier As Integer
colLetter = "AB"
multiplier = 0
'If there is more than one letter, that means it's gone through the whole alphabet
If Len(colLetter) > 1 Then
multiplier = Asc(Left(UCase(colLetter), 1)) - 64
End If
colNumber = (multiplier * 26) + Asc(Right(UCase(colLetter), 1)) - 64
Debug.Print colNumber
End Sub
What is the algorithm to convert an Excel Column Letter into its Number?
public static int ExcelColumnNameToNumber(string columnName)
{
if (string.IsNullOrEmpty(columnName)) throw new ArgumentNullException("columnName");
columnName = columnName.ToUpperInvariant();
int sum = 0;
for (int i = 0; i < columnName.Length; i++)
{
sum *= 26;
sum += (columnName[i] - 'A' + 1);
}
return sum;
}
How do I convert a spreadsheet letternamed column coordinate to an integer?
I have found particularly neat way to do this conversion:
def index_from_column_name(colname)
s=colname.size
(colname.to_i(36)-(36**s-1).div(3.5)).to_s(36).to_i(26)+(26**s-1)/25-1
end
Explanation why it works
(warning spoiler ;) ahead). Basically we are doing this
(colname.to_i(36)-('A'*colname.size).to_i(36)).to_s(36).to_i(26)+('1'*colname.size).to_i(26)-1
which in plain English means, that we are interpreting colname as 26-base number. Before we can do it we need to interpret all A's as 1, B's as 2 etc. If only this is needed than it would be even simpler, namely
(colname.to_i(36) - '9'*colname.size).to_i(36)).to_s(36).to_i(26)-1
unfortunately there are Z characters present which would need to be interpreted as 10(base 26) so we need a little trick. We shift every digit 1 more then needed and than add it at the end (to every digit in original colname)
`
Related Topics
Stubbing Controller Actions in Rspec Request Specs
Rails, Ruby, How to Count and Sort, Display Top Results
How to Transform the Utf8 Chars to Iso8859-1
How to Specify Formatting Options for To_Yaml in Ruby
When Is the Do Keyword Required in Ruby
How to Get the Nth Element of an Enumerable in Ruby
Gem Install Therubyracer -V 0.11.4 Fails on Os X 10.10
Rails Syntax Error: Unexpected Keyword_Ensure, Expecting Keyword_End
Intelligently Generating Combinations of Combinations
Ruby/Rails Actionmailer Not Working with Ntlm
Ruby: Module, Mixins and Blocks Confusing
Regular Expression to Match Only the First File in a Rar File Set
Rails Object Based Permission/Authorization Engine
Pg_Dump: [Archiver (Db)] Query Failed: Error: Permission Denied for Relation Abouts
Change Emacs Ruby-Mode Indent to 4 Spaces
What Does "String Literal in Condition" Mean