# Algorithm needed:  1-26=A-Z, then AA-AZ, BA-BZ...ZZA-ZZZ, AAAA, etc.

**URL:** <https://boards.straightdope.com/t/algorithm-needed-1-26-a-z-then-aa-az-ba-bz-zza-zzz-aaaa-etc/216411>\
**Category:** Factual Questions\
**Created:** [December 2, 2003, 3:23am UTC](https://boards.straightdope.com/t/algorithm-needed-1-26-a-z-then-aa-az-ba-bz-zza-zzz-aaaa-etc/216411 "2003-12-02T03:23:15Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![AHunter3](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/ahunter3/32/368_2.png) [@AHunter3](https://boards.straightdope.com/u/AHunter3)\
**Post date:** [December 2, 2003, 3:23am UTC](https://boards.straightdope.com/t/algorithm-needed-1-26-a-z-then-aa-az-ba-bz-zza-zzz-aaaa-etc/216411/1 "2003-12-02T03:23:15Z")

</div>

I’m trying to set up a calc formula that will reference a number field and convert it to letters (“alphabase”, or base 26).

I’ve got a perfectly splendid simple little algorithm that works fine for anything I throw at it, of any size – except that it treats “Z” as zero, creating some counterintuitive ordinal patterns like “AW, AX, AY, BZ, BA”.

Then I’ve got three different calc formulas that try to wrestle the results into the desired ordinal pattern (A-Z, then AA, AB…AZ, then BA, BB…BZ, CA-CZ, etc to YA-YZ, ZA-AA, then AAA, AAB…AAZ…AZZ, BAA…ZZY…ZZZ, AAAA, you get the picture).

I will post the one that “runs” correctly the longest, generating its first undesired results at 35828 (which in alphabase = 2 1 0 0 where each column is 26 times the value of the column to its right), at which point it yields BZYZ instead of AYYZ following AYYY. I don’t care for the code itself, though – it has more brute force than elegance about it if you know what I mean. I’m getting errors in the 676’s column as well as the 17576’s column at 2 1 0 0 and have yet to get far enough into checking and debugging to even look at columns yet farther to the left.

I’m convinced there must be a simple and reliable formula that for some reason I’ve simply failed to visualize, so if you know one, post one! Thanks! (I’m doing this in FileMaker but a lot of techniques are portable between environments)

- 

Note about the Choose function: “Choose” is a zero-indexed function, e.g., Choose(weekday, “zilch”, “sunday”, “monday”, “tuesday”, “wednesday”, “thursday”, “friday”, “saturday”) returns “saturday” when weekday=7 and returns “sunday” when weekday=1; if you put 0 in for weekday you get back “zilch”.  
\*

```auto

SN: original (decimal) serial number
Units: Mod(SN, 26)
A 26s: Int(Mod( SN/26, 26))
A 676s: Int(Mod( SN/676, 26))
A 17576s: Int(Mod( SN/17576, 26))
A 456976s: Int(Mod( SN/456976, 26))
 

Conversion to Alpha formula (the ugly brute force thing):

Case(SN¾475254, "", 
Choose(A 456976s-Case(A 456976s=0, 1)
, "Z", "A", "B", "C", "D", "E", "F", "G", "H", "I", 
"J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", 
"T", "U", "V", "W", "X", "Y"))

&

Case(SN¾18278,"", A 17576s=0 and A 676s=0, "Y",
Choose(A 17576s-Case(A 676s=0, 1)
, "Z", "A", "B", "C", "D", "E", "F", "G", "H", "I", 
"J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", 
"T", "U", "V", "W", "X", "Y"))

&
Case(SN¾702,"", (SN>17602 and SN<18279), "Z", A 676s=0, "Y",
Choose(A 676s-Case(A 26s=0 or (A 26s=1 and Units=0), 1)
, "Z", "A", "B", "C", "D", "E", "F", "G", "H", "I", 
"J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", 
"T", "U", "V", "W", "X", "Y"))

&

Case(SN¾26,"", A 26s=0 and Units=0, "Y",
Choose(A 26s-Case(Units=0, 1)
, "Z", "A", "B", "C", "D", "E", "F", "G", "H", "I", 
"J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", 
"T", "U", "V", "W", "X", "Y"))

&

Choose(Units
, "Z", "A", "B", "C", "D", "E", "F", "G", "H", "I", 
"J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", 
"T", "U", "V", "W", "X", "Y")

```

---

<div class="post-metadata">

**Author:** ![bup](https://avatars.discourse-cdn.com/v4/letter/b/6bbea6/32.png) [@bup](https://boards.straightdope.com/u/bup)\
**Post date:** [December 2, 2003, 3:41am UTC](https://boards.straightdope.com/t/algorithm-needed-1-26-a-z-then-aa-az-ba-bz-zza-zzz-aaaa-etc/216411/2 "2003-12-02T03:41:10Z")

</div>

In the left-most column, ’ ’ is 0, ‘A’ is 1, ‘B’ is 2, etc. In any other column, ‘A’ is 0, ‘B’ is 1, etc.

So you have to determine the number of columns first.

---

<div class="post-metadata">

**Author:** ![Inoshiro](https://avatars.discourse-cdn.com/v4/letter/i/a9adbd/32.png) [@Inoshiro](https://boards.straightdope.com/u/Inoshiro)\
**Post date:** [December 2, 2003, 5:00am UTC](https://boards.straightdope.com/t/algorithm-needed-1-26-a-z-then-aa-az-ba-bz-zza-zzz-aaaa-etc/216411/3 "2003-12-02T05:00:42Z")

</div>

#!/usr/bin/perl

I just wrote up this perl script. It’s recursive and should convert any number of any length into the A-Z format you want. I don’t know Filemaker. Notes on syntax: the “.” is the string catenation operator. “my” creates a local variable. I think this is what you want right?

$ALPHA = “ABCDEFGHIJKLMNOPQRSTUVWXYZ”;

print "The alpha value of the number is " . base26($ARGV[0]) . "  
";

sub base26 {  
my $n = shift;  
my $d = int($n/26);  
my $r = $n % 26;

```
if ($d != 0) {
$alpha = base26($d) . substr($ALPHA,$r,1);
return $alpha;
}
else {
return substr($ALPHA,$r,1);
}

```

}

---

<div class="post-metadata">

**Author:** ![ultrafilter](https://avatars.discourse-cdn.com/v4/letter/u/3d9bf3/32.png) [@ultrafilter](https://boards.straightdope.com/u/ultrafilter)\
**Post date:** [December 2, 2003, 5:15am UTC](https://boards.straightdope.com/t/algorithm-needed-1-26-a-z-then-aa-az-ba-bz-zza-zzz-aaaa-etc/216411/4 "2003-12-02T05:15:56Z")

</div>

It’s base 26. Use Horner’s method with A = 1, …, Z = 26.

Horner’s method is used to quickly evaluate a polynomial a[sub]n[/sub]x[sup]n[/sup] + … + a[sub]0[/sub] at a particular value of x.

Here’s pseudocode:

p = a[sub]n[/sub]  
for n - 1 \> i \> 0  
&nbsp;&nbsp;&nbsp;&nbsp;p = p \* 26  
&nbsp;&nbsp;&nbsp;&nbsp;p = p + a[sub]i[/sub]

That’ll do it.

---

<div class="post-metadata">

**Author:** ![John\_T.Conklin](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/john_t.conklin/32/3307_2.png) [@John\_T.Conklin](https://boards.straightdope.com/u/John_T.Conklin)\
**Post date:** [December 2, 2003, 5:47am UTC](https://boards.straightdope.com/t/algorithm-needed-1-26-a-z-then-aa-az-ba-bz-zza-zzz-aaaa-etc/216411/5 "2003-12-02T05:47:21Z")

</div>

> [@](#):
>
> \*Originally posted by AHunter3 \*  
> \*\*I’m convinced there must be a simple and reliable formula that for some reason I’ve simply failed to visualize, so if you know one, post one! Thanks! (I’m doing this in FileMaker but a lot of techniques are portable between environments)  
> \*\*

The Z -\> AA, ZZ -\> AAA, ZZZ -\> AAAA transitions had me stuck for a while (which seems to be a problem in the good Doctor’s perl program), but the following C++ code seems to work.

It uses the fairly common idiom for printing base N values by constructing the value from least to greatest significant digit in an array, and then reversing it for presentation (in this case, returing it as a string).

```auto

#include <iostream>
#include <string>
#include <vector>
#include <algorithm>
#include <iterator>

std::string
conv(unsigned long val)
{
    static const char digits[] = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
    std::vector<char> buf;

    buf.push_back(digits[val % 26]);
    val /= 26;

    if (val) {
        do {
            --val;
            buf.push_back(digits[val % 26]);
            val /= 26;
        } while (val);
    }
    // copy constructed value into string.
    // by iterating through the buffer in reverse order, the
    // resulting string will be in the correct order.
    std::string s;
    std::copy(buf.rbegin(), buf.rend(), std::back_inserter(s));

    return s;
}

```

---

<div class="post-metadata">

**Author:** ![ultrafilter](https://avatars.discourse-cdn.com/v4/letter/u/3d9bf3/32.png) [@ultrafilter](https://boards.straightdope.com/u/ultrafilter)\
**Post date:** [December 2, 2003, 6:01am UTC](https://boards.straightdope.com/t/algorithm-needed-1-26-a-z-then-aa-az-ba-bz-zza-zzz-aaaa-etc/216411/6 "2003-12-02T06:01:15Z")

</div>

> [@](#):
>
> \*Originally posted by ultrafilter \*  
> \*\*It’s base 26. Use Horner’s method with A = 1, …, Z = 26.
> 
> Horner’s method is used to quickly evaluate a polynomial a[sub]n[/sub]x[sup]n[/sup] + … + a[sub]0[/sub] at a particular value of x.
> 
> Here’s pseudocode:
> 
> p = a[sub]n[/sub]  
> for n - 1 \> i \> 0  
> &nbsp;&nbsp;&nbsp;&nbsp;p = p \* 26  
> &nbsp;&nbsp;&nbsp;&nbsp;p = p + a[sub]i[/sub]
> 
> That’ll do it. \*\*

Shit, I did it backwards. Teach me to skim.

Here’s pseudocode. A is an array of sufficient length, d is the input, and % is the modulus operator.

while d != 0  
&nbsp;&nbsp;&nbsp;&nbsp;A[sub]i[/sub] = d % 26  
&nbsp;&nbsp;&nbsp;&nbsp;d = d / 26

Then just print the entries of A backwards. Keep a counter so that you know where to start, and print 0 if d is 0.

This is what **John T. Conklin** did, but the pseudocode may be easier to adapt than C++ code.

---

<div class="post-metadata">

**Author:** ![Algorithm](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/algorithm/32/12204_2.png) [@Algorithm](https://boards.straightdope.com/u/Algorithm)\
**Post date:** [December 2, 2003, 6:03am UTC](https://boards.straightdope.com/t/algorithm-needed-1-26-a-z-then-aa-az-ba-bz-zza-zzz-aaaa-etc/216411/7 "2003-12-02T06:03:08Z")

</div>

It’s so nice to feel needed!

---

<div class="post-metadata">

**Author:** ![Bob55](https://avatars.discourse-cdn.com/v4/letter/b/e9c0ed/32.png) [@Bob55](https://boards.straightdope.com/u/Bob55)\
**Post date:** [December 2, 2003, 6:24am UTC](https://boards.straightdope.com/t/algorithm-needed-1-26-a-z-then-aa-az-ba-bz-zza-zzz-aaaa-etc/216411/8 "2003-12-02T06:24:54Z")

</div>

I could do this, but only on the TI-82.

---

<div class="post-metadata">

**Author:** ![yabob](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/yabob/32/2821_2.png) [@yabob](https://boards.straightdope.com/u/yabob)\
**Post date:** [December 2, 2003, 6:58am UTC](https://boards.straightdope.com/t/algorithm-needed-1-26-a-z-then-aa-az-ba-bz-zza-zzz-aaaa-etc/216411/9 "2003-12-02T06:58:31Z")

</div>

Careful - it isn’t exactly base 26, or base anything. There is no 0 placeholder, so you have 26 \*\* 2 two digit “numbers”, not 25\*26, and so on (ie, AA means something distinct from A, AB != B, and so on - ZZ = 702, not 26\*\*2). I’ve done this thing a couple of times and always had to play a bit to get it to work right. The “–val” in **John T. Conklin** ’s routine is crucial. A display routine I’m currently using:

```auto

 private static void alphaseq0(int n, String alphabet, StringBuffer buf) {
        int len = alphabet.length();
        
        if (n >= len) {
               alphaseq0(n/len - 1,alphabet,buf);
               n = n % len;
        }
        
        buf.append(alphabet.charAt(n));
}
 

```

That’s biased so that 0=A, 1=B, and so on. Note that I have that - 1 cropping up in my recursion, too. Produce the string backwards, and you won’t have to do the recursion. I wasn’t that worried about efficiency, and the recursion is clearer.

---

<div class="post-metadata">

**Author:** ![AHunter3](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/ahunter3/32/368_2.png) [@AHunter3](https://boards.straightdope.com/u/AHunter3)\
**Post date:** [December 3, 2003, 1:49am UTC](https://boards.straightdope.com/t/algorithm-needed-1-26-a-z-then-aa-az-ba-bz-zza-zzz-aaaa-etc/216411/10 "2003-12-03T01:49:53Z")

</div>

**bup** :

> [@](#):
>
> In the left-most column, ’ ’ is 0, ‘A’ is 1, ‘B’ is 2, etc. In any other column, ‘A’ is 0, ‘B’ is 1, etc.

This comment put me on the right track. Instead of staring at the formula, I spent some time looking at the data and making plain-english notations describing the circumstances where I wanted the letters to be different from the ones I was getting.

The key was learning how to scale this portion of the formula for 26s so as to apply the same logic to the columns farther leftwards:

Case(Units=0, 1)

This case statement describes how much (if anything) to subtract from the raw base-26 value in order to get the correct Choose digit for obtaining the letter. I knew it had to scale somehow but I didn’t have a sense or feel for how the pattern would develop.

Here’s the Case statements that worked:

```auto

for 676s: Case(A 26s=0 or A 26s*26+Units<27, 1)

for 17576s: Case(A 676s=0 or A 676s*676
     +A 26s*26+Units<703, 1)

for 456976s: Case(A 17576s=0 or A 17576s*17576+A 676s*676+
     A 26s*26+Units<18279, 1)

for 11881376s: Case(A 456976s=0 or A 456976s*456976+A 17576s*17576+
   A 676s*676+A 26s*26+Units<475255, 1)

```

and so on. It’s easy once you know the pattern!

---

<div class="post-metadata">

**Author:** ![AHunter3](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/ahunter3/32/368_2.png) [@AHunter3](https://boards.straightdope.com/u/AHunter3)\
**Post date:** [December 3, 2003, 1:54am UTC](https://boards.straightdope.com/t/algorithm-needed-1-26-a-z-then-aa-az-ba-bz-zza-zzz-aaaa-etc/216411/11 "2003-12-03T01:54:22Z")

</div>

I want to thank the rest of you folks as well. Some of the procedures described would work in FileMaker as a verb (i.e., as a Script) but I wanted a noun (i.e., a Field Definition).