+ Reply to Thread
Results 1 to 2 of 2

Get Column Letter from Column Number

  1. #1
    Gary Brown
    Guest

    Get Column Letter from Column Number

    Does anyone know of an easy way of getting the Column Letter if you know the
    Column Number?
    ie: a function that returns "D" if I input 4.
    I've written a UDF but is there a better way?
    I've listed my UDF below.
    TIA,
    --
    Gary Brown
    [email protected]

    '/==============================================/
    Private Function ColumnLetterFromNumber(iColNumber As Long) _
    As String
    'this function converts column number into letters
    'this is designed to only work thru ZZ (702 columns),
    ' however, currently Excel only goes to IV (256 columns)
    'Syntax: =ColumnLetterFromNumber(4)
    ' returns 'D'
    'Gary Brown 05/11/2000

    Dim str1stLetter As String, str2ndLetter As String

    Application.Volatile True

    ColumnLetterFromNumber = ""

    On Error GoTo err_Function

    If iColNumber <= 26 Then
    str1stLetter = ""
    Else
    str1stLetter = Chr(Int(iColNumber / 26.001) + 64)
    End If

    str2ndLetter = _
    Chr((iColNumber - _
    (26 * Int(iColNumber / 26.001))) + 64)

    ColumnLetterFromNumber = str1stLetter & str2ndLetter

    exit_Function:
    Exit Function

    err_Function:
    Debug.Print "Error: " & Err.Number & " - " & Err.Description
    ColumnLetterFromNumber = ""
    GoTo exit_Function

    End Function
    '/==============================================/




  2. #2
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Get Column Letter from Column Number

    Quote Originally Posted by Gary Brown View Post
    Does anyone know of an easy way of getting the Column Letter if you know the
    Column Number?......
    Just for Fun to answer a 10 year old Thread! And also for anyone stumbling accross/ googling on the Thread when having a similar Question, as i did.
    see here:
    http://www.excelforum.com/tips-and-t...explained.html
    Alan
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1