Reply
Thread Tools Display Modes
#1
Old 12-01-2003, 10:23 PM
Charter Member
Join Date: Mar 1999
Location: NY (Manhattan) NY USA
Posts: 19,125
Algorithm needed: 1-26=A-Z, then AA-AZ, BA-BZ...ZZA-ZZZ, AAAA, etc.

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

Code:
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(SN475254, "", 
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(SN18278,"", 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(SN702,"", (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(SN26,"", 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")
__________________
Disable Similes in this Post

---
Omar Little suggests lunatics like me include a disclaimer
#2
Old 12-01-2003, 10:41 PM
bup bup is offline
Guest
Join Date: Sep 1999
Location: glenview,il,usa
Posts: 11,905
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
Old 12-02-2003, 12:00 AM
Guest
Join Date: Jan 2003
Posts: 258
#!/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]) . "\n";

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
Old 12-02-2003, 12:15 AM
Guest
Join Date: May 2001
Location: In another castle
Posts: 18,988
It's base 26. Use Horner's method with A = 1, ..., Z = 26.

Horner's method is used to quickly evaluate a polynomial anxn + ... + a0 at a particular value of x.

Here's pseudocode:

p = an
for n - 1 > i > 0
&nbsp;&nbsp;&nbsp;&nbsp;p = p * 26
&nbsp;&nbsp;&nbsp;&nbsp;p = p + ai

That'll do it.
#5
Old 12-02-2003, 12:47 AM
Guest
Join Date: Feb 2000
Location: South San Francisco, CA
Posts: 147
Re: Algorithm needed: 1-26=A-Z, then AA-AZ, BA-BZ...ZZA-ZZZ, AAAA, etc.

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

Code:
#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
Old 12-02-2003, 01:01 AM
Guest
Join Date: May 2001
Location: In another castle
Posts: 18,988
Quote:
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 anxn + ... + a0 at a particular value of x.

Here's pseudocode:

p = an
for n - 1 > i > 0
&nbsp;&nbsp;&nbsp;&nbsp;p = p * 26
&nbsp;&nbsp;&nbsp;&nbsp;p = p + ai

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;Ai = 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.
#7
Old 12-02-2003, 01:03 AM
Guest
Join Date: Jul 2003
Posts: 1,239
It's so nice to feel needed!
#8
Old 12-02-2003, 01:24 AM
Guest
Join Date: Sep 2002
Location: Orlando, FL
Posts: 1,179
I could do this, but only on the TI-82.
#9
Old 12-02-2003, 01:58 AM
Charter Member
Join Date: Mar 2000
Posts: 7,788
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:
Code:
 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
Old 12-02-2003, 08:49 PM
Charter Member
Join Date: Mar 1999
Location: NY (Manhattan) NY USA
Posts: 19,125
Got it

bup:
Quote:
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:
Code:
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!
__________________
Disable Similes in this Post

---
Omar Little suggests lunatics like me include a disclaimer
#11
Old 12-02-2003, 08:54 PM
Charter Member
Join Date: Mar 1999
Location: NY (Manhattan) NY USA
Posts: 19,125
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).
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump


All times are GMT -5. The time now is 12:46 PM.

Copyright © 2017
Best Topics: pan breaks navy dress khaki dumpster capitalized dogs are nasty buy dolphin summer camp showers mfg abbreviation markers for skin serial port laptops pig weiner wanker definition popeye and bruto hris langan gate open toes picking up bong and hookah affectionate kitten ear candles mythbusters determining scale 1.88 m very delicious use of washers small cabover trucks proulx pronunciation codiene in canada anice inn large roundabouts nabisco bacon crackers swingin dick rhoda meaning babylon 5 vorlons euphemisms for drunk hebe jewish wooden drawer lubricant difference between boric acid and borax who makes snap on air tools automotive service advisor salary how many holes does a woman have csi cyber theme song lyrics do i need a prescription for antibiotics how much kinetic energy is lost in the collision how to get a doctor to prescribe oxycodone 30 mg lewis gun without shroud other than that mrs lincoln pelvic fracture recovery time elderly fip contagious to other cats turn gold into white gold slavery in the byzantine empire where can i cash a personal check without id how to remove rust from shower curtain hooks joke went over your head what do scotsmen wear under their kilts stun gun for dogs where did my youtube favorites go were the puritans puritanical how long can you keep cooked potatoes japanese maple leaf vs pot leaf what does it mean to write something off cheap way to heat a pool push pull water shut off valves life alert lady on floor boss hoss 502 top speed amazon group my items into as few shipments as possible how to have wet dreams on purpose how to clean a gold watch another word for 100 years difference between trousers and slacks