Home > Uncategorized > Convert a number to its equivalent letter

Convert a number to its equivalent letter

December 17th, 2005 Leave a comment Go to comments

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

‘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

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.

Share
[CiteULike] [del.icio.us] [Digg] [Facebook] [Furl] [Google] [Reddit] [Slashdot] [StumbleUpon] [Technorati] [Twitter] [Email] More »
Powered by Bookmarkify™
Categories: Uncategorized Tags:
  1. Anonymous
    May 5th, 2008 at 13:40 | #1
    Reply | Quote

    Perfect Function , Exaclty what i needed!!!! Dalik

  2. 7th-key
    September 19th, 2008 at 09:50 | #2
    Reply | Quote

    Thank you!

  1. No trackbacks yet.