Excel: Get Letter from Number


Hi I have some code that gets the letter to the corresponding number


Function GetLetter(lNum As Long) As String
  If lNum <= 26 Then
    GetLetter = Chr(lNum + 64)
  Else
    GetLetter = Chr(Int((lNum - 1) / 26) + 64) & Chr(((lNum - 1) Mod 26) + 65)
  End If
End Function
I'm trying to do a similar thing but not with the alphabet, only with a string of letters in the alphabet. My string is "ABCDFGHIJKMQRSUVWXYZ" 20 characters. if the number is 22 this will return "AB" and 42 will return "BB" which is also correct. However after ZZ it goes wrong. of rexample if the number is 522 the it returns "BB" Hope this makes sense, here's my code so far.

Function GetAvailableLetter(iNum As Long) As String
Dim iCount As Long, iMax As Long, iPos As Long, strSearch As String
  
  strSearch = "ABCDFGHIJKMQRSUVWXYZ"
  iMax = Len(strSearch)
    
  If iNum > iMax Then
    iPos = Int((iNum - 1) / iMax)
    If iPos > iMax Then
      iPos = iNum Mod iMax
      GetAvailableLetter = GetAvailableLetter & Mid(strSearch, iPos, 1)
    End If
  End If
  
  iPos = iNum Mod iMax
  iPos = IIf(iPos = 0, iMax, iPos)
  GetAvailableLetter = GetAvailableLetter & Mid(strSearch, iPos, 1)
  
End Function


This question generated 28 answers. To proceed to the answers, click here.

This thread is current as of June 27, 2014.


For more resources for Microsoft Excel