Wrox Programmer Forums Formula
 |
 Pro VB 6 For advanced Visual Basic coders working in version 6 (not .NET). Beginning-level questions will be redirected to other forums, including Beginning VB 6.
 Welcome to the p2p.wrox.com Forums. You are currently viewing the Pro VB 6 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com

September 5th, 2003, 10:26 AM
 Ned Authorized User Join Date: Jun 2003 Posts: 66 Thanks: 0 Thanked 0 Times in 0 Posts
Formula

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)

-ned
__________________
-Ned

September 8th, 2003, 03:47 AM
 pgtips Friend of Wrox Join Date: Jun 2003 Posts: 1,212 Thanks: 0 Thanked 1 Time in 1 Post

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

September 10th, 2003, 10:26 AM
 Ned Authorized User Join Date: Jun 2003 Posts: 66 Thanks: 0 Thanked 0 Times in 0 Posts

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

 Similar Threads Thread Thread Starter Forum Replies Last Post Help with formula Juan0214 Access 4 August 1st, 2008 07:25 AM Help with formula 2 Juan0214 Access 3 July 10th, 2008 06:52 PM Formula scandalous Access VBA 11 February 27th, 2007 09:49 AM formula sinha Crystal Reports 1 October 26th, 2005 12:53 PM Help with a formula Corey Access 2 October 20th, 2005 10:48 AM