Saturday, December 17, 2005
Convert a number to its equivalent letter
If you want to convert a number to its equivalent letter, for e.g. 5 to E, you can use two approaches.
1. Insert this code in a Module and use this function
e.g. "=Columnletter(5)" will return E and "= Columnletter(COLUMN())" will return letter depending on which column this formula is in.
2. Use this formula (Credit due) "=LEFT(ADDRESS(1,COLUMN(),4),1)"
These formulas can be useful with functions like INDIRECT and OFFSET.
1. Insert this code in a Module and use this function
'Author: Alan L. Lesmerises
'From: http://www.freevbcode.com/ShowCode.asp?ID=4303
Function ColumnLetter(ColumnNumber As Integer) As String
If ColumnNumber > 26 Then
' 1st character: Subtract 1 to map the characters to 0-25,
' but you don't have to remap back to 1-26
' after the 'Int' operation since columns
' 1-26 have no prefix letter
' 2nd character: Subtract 1 to map the characters to 0-25,
' but then must remap back to 1-26 after
' the 'Mod' operation by adding 1 back in
' (included in the '65')
ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
Chr(((ColumnNumber - 1) Mod 26) + 65)
Else
' Columns A-Z
ColumnLetter = Chr(ColumnNumber + 64)
End If
End Function
'From: http://www.freevbcode.com/ShowCode.asp?ID=4303
Function ColumnLetter(ColumnNumber As Integer) As String
If ColumnNumber > 26 Then
' 1st character: Subtract 1 to map the characters to 0-25,
' but you don't have to remap back to 1-26
' after the 'Int' operation since columns
' 1-26 have no prefix letter
' 2nd character: Subtract 1 to map the characters to 0-25,
' but then must remap back to 1-26 after
' the 'Mod' operation by adding 1 back in
' (included in the '65')
ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
Chr(((ColumnNumber - 1) Mod 26) + 65)
Else
' Columns A-Z
ColumnLetter = Chr(ColumnNumber + 64)
End If
End Function
e.g. "=Columnletter(5)" will return E and "= Columnletter(COLUMN())" will return letter depending on which column this formula is in.
2. Use this formula (Credit due) "=LEFT(ADDRESS(1,COLUMN(),4),1)"
These formulas can be useful with functions like INDIRECT and OFFSET.
