2004.12.06 08:37 PM

Alpha Bullets

Somehow in twenty years of doing this I've never had to derive an alpha value from a number in the same way that a word processor (or even HTML) labels alpha bullets or a spreadsheet labels columns, which is to say A-Z for the first 26, then A-Z as a prefix to A-Z for each subsequent set of 26, as in:

1=A
2=B
...
25=Y
26=Z
27=AA
28=AB
...
701=ZY
702=ZZ

Having done it today (for some obscure thing), I figured I'd share.

Public Function GetAlphaBullet(ByVal Index As Integer) As String

  Const LETTERS As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

  Dim lPrefix As Integer
  Dim sPrefix As String
  
  If Index < 1 Or Index > 702 Then Exit Function
  
  lPrefix = Fix((Index - 1) / 26)
  If lPrefix > 0 Then sPrefix = Mid$(LETTERS, lPrefix, 1)
  
  GetAlphaBullet = sPrefix & Mid$(LETTERS, ((Index - 1) Mod 26) + 1, 1)

End Function

After making and testing a first pass, I ran it by my admittedly smarter pal Darnley Bynoe who simplified my math with the Fix (don't ask what I was doing) and added a constant string of letters (my original use of an offset from Chr(Asc("A")) was clear enough, but really unnecessary).

I suppose the next thing to do is eliminate the 26*27 upper limit through recursion. ;)


Comments

What does "fix" do? I want it!

Jeff Key | 2004.12.07 07:07 AM

Jeff - I can't tell when you're pulling my leg! ;)

Just in case, it's the same as VBA.Conversion.Int (or .NET's System.Math.Floor), except that Fix is asymmetric. I could have used either. The main point was that use of the Fix (or Int) eliminated some less than clear Mod'ing I was doing to find the letter group for the prefix.

ewbi.develops | 2004.12.07 07:24 AM

Hell, I could have just used integer division (\ vs. /) and done without Fix or Int.

ewbi.develops | 2004.12.08 03:30 PM

Darnley answered the call for recursion:

http://ewbi.blogs.com/develops/2005/02/alpha_bullets_p.html

Thanks!

ewbi.develops | 2005.02.10 01:36 PM



Post a Comment

 
  (optional)
  (no html)
 
   


TrackBack

TrackBack URL:  http://www.typepad.com/services/trackback/6a00d8341c7bd453ef00d834213f5d53ef

Listed below are links to weblogs that reference Alpha Bullets:

» Google Groups. From Useful to Useless. from { public virtual blog
Google just had to go and mess up a great thing. Google Groups was the greatest thing around, until Google decided to mess it up and make it totally useless. [Read More]

Tracked on Dec 6, 2004 11:09:43 PM

» Google Groups. From Useful to Useless. from { public virtual blog
Google just had to go and mess up a great thing. Google Groups was the greatest thing around, until Google decided to mess it up and make it totally useless. [Read More]

Tracked on Dec 6, 2004 11:10:18 PM