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

Factual Questions
1

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”.
*



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")
2

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.

3

#!/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);
}

}

4

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
    p = p * 26
    p = p + a[sub]i[/sub]

That’ll do it.

5

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



#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;
}
6

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
    A[sub]i[/sub] = d % 26
    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.

7

It’s so nice to feel needed!

8

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

9

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:



 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.

10

bup:

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:



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!

11

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