Formula

Ned · September 5th, 2003, 10:26 AM

Hi there,
Does anybody have a small routine which can give me the alphabets for the given number - for example:
If I send number 1 - function should return A
2 - B, 3 - C ... 27 - Z
28 - AA, 29 - AB, ... AZ
next no - BA, next no - BB ... BZ ... ZZ
next no - AAA, ... ZZZ (this is maximum I want, not more than three letter)

Thanx in advance,

-ned

pgtips · September 8th, 2003, 03:47 AM

Try this recursive solution.

Code:

Function foo(ByVal n As Integer) As String

  Dim nChar

  If n > 0 Then

    nChar = n Mod 26
    If nChar = 0 Then
      nChar = 26
      foo = foo((n - 1) \ 26) & Chr(64 + nChar)
    Else
      foo = foo(n \ 26) & Chr(64 + nChar)
    End If

  Else

    foo = ""

  End If

End Function

BTW I don't know if you have a different alphabet in Canada, but here in the UK "Z" is 26th letter not 27th ;)

rgds
Phil

Ned · September 10th, 2003, 10:26 AM

Phil,
Wonderfull job, it works.
Thanks,
-ned