## 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. ;)

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