# Recursive function to get a number to characters

## Recommended Posts

This one may get a little complicated very quickly, so I apologize if I don't explain this well enough to follow.

I'm working with a lettering system that goes from A-Z in increasing value. When we hit Z, the next value on the list becomes AA and goes to AZ, where the next value becomes BA-BZ, etc. To keep these combinations in order and sorted, I developed a recursive function for assigning them each a unique number value. The 2 functions to do this are:

StringToNum(MyString)=

```Let( [Values= "ABCDEFGHIJKLMNOPQRSTUVWXYZ"];

Case( Length(MyString) > 1; Position ( Values ; Right( MyString; 1) ; 1 ; 1 )* 1 + StringToNumMult(Left( MyString; Length(Mystring)-1) ; 100);

Position ( Values ; Left( MyString; 1) ; 1 ; 1 ))
)```

StringToNumMult(MyString; Multiplier)=

```Let( [Values= "ABCDEFGHIJKLMNOPQRSTUVWXYZ"];

Case( Length(MyString) > 1; Position ( Values ; Right( MyString; 1) ; 1 ; 1 )* Multiplier + StringToNumMult(Left( MyString; Length(Mystring)-1) ; Multiplier * 100);

Position ( Values ; Left( MyString; 1) ; 1 ; 1 )* Multiplier)
)```

This could all be done as one function, the second, using a multiplier of 1 to start, but it makes more sense to a user to only input a string. Basically, I take the first letter on the right, find it's position in the alphabet, and assign it that value. Since there are 26 letters, I can't have them repeat values when converted, so I multiply each character position by 100.

So A=1, Z=26, AB=102, BD=204, AAA=10101, etc. This is working and should be relatively straightforward. Now to the tricky part.

I have to now take this number and figure out what the NEXT one in the sequence would be. It's possible that I can work directly with the string I started from, but something told me that the number would be easier.

So when I have a Z, I need to result of the calculation to be 101 to indicate I need an AA next, and eventually get that directly from my list of values instead of outputting the number 101 at all.

I started by attempting to set up a new recursive function like this:

NumToNextString(MyNumString)=

```Let( [Values= "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
RightVal=GetAsNumber ( Right ( GetAsText ( MyNumString); 2 ))];

Case( Length ( MyNumString) > 1;

Case( RightVal =26;

Case ( GetAsNumber( Middle ( GetAsText (MyNumString) ; Length(MyNumString)-4 ; 2 )) =26;

NumToNextStringMult( Left(GetAsText (MyNumString+75) ; Length(MyNumString)-2); 1000) & "A";
NumToNextStringMult( Left(GetAsText (MyNumString) ; Length(MyNumString)-2); 1000) & Middle( Values; RightVal+1; 1))

; Middle( Values; MyNumString+1; 1))

; Middle( Values; MyNumString+1; 1))
)```

NumToNextStringMult(MyNumString;Multiplier)

```Let( [Values= "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
RightVal=GetAsNumber ( Right ( GetAsText ( MyNumString); 2 ))];

Case( Length ( MyNumString) > 1;

Case( RightVal =26;

Case ( GetAsNumber( Middle ( GetAsText (MyNumString) ; Length(MyNumString)-4 ; 2 )) =26;

NumToNextStringMult( Left(GetAsText (MyNumString+75) ; Length(MyNumString)-2); Multiplier*74) & "A";
NumToNextStringMult( Left(GetAsText (MyNumString) ; Length(MyNumString)-2); Multiplier*74) & Middle( Values; RightVal+1; 1))

; NumToNextStringMult( Left(GetAsText (MyNumString) ; Length(MyNumString)-2); Multiplier*74) & Middle(Values; RightVal+1; 1))

; Middle(Values; MyNumString+1; 1))
)```

This of course doesn't work. It processes up to Z, but then the recursion seems to be getting trapped in a loop using AA that it returns blank for, possibly because I'm passing in the value wrong to the mult function?

Can anybody see a fix or come up with a better way of addressing this issue? I keep getting slightly closer, but I seem to just be spinning my wheels at the moment.

##### Share on other sites

Do you mean like this?

http://www.briandunning.com/cf/399

##### Share on other sites

I did a formula for going from underlying serial number to a display-field of AA, AB, ...AZ, BA, BB, BC....ZX, ZY, ZZ, AAA, etc.

I did mine back in the FileMaker 6 era when we didn't have custom functions. Just Calcuations from Hell

##### Share on other sites

Gah!

I can't tell you how many times I wanted to smack my head into the keyboard yesterday from this. I almost had my convoluted setup working when it dawned on me to start working with base 26 and the Mod() function, and then to see this post pop up!

I even looked through that site, but must not have entered the correct keywords.

Thanks Comment! This is a huge help!

##### Share on other sites

It's actually not base 26, by the way; that's what makes it so damn complicated. There's no equivalent of a 0. If Z were 0, you'd get sequences like AX, AY, BZ, BA. You don't. A is obviously not 0 either. Technically, it's not base anything, it's a weird convention that's loosely grounded in base 26, but with offsets and stuff.

If Brian Dunning's short-n-sweet custom fx really does the trick, that's an amazing and sophisticated piece of programming he did.

##### Share on other sites
If Brian Dunning's short-n-sweet custom fx really does the trick, that's an amazing and sophisticated piece of programming he did.

Check the author of that CF, Alan. It's our old friend comment thinking in base 26 (or whatever).

##### Share on other sites

Color me impressed!

##### Share on other sites
It's our old friend comment thinking in base 26 (or whatever)

it's a good thing he wields that power just for filemaker. heaven knows what would happen if he turned his attention to, say, world domination.

##### Share on other sites

Who says he's not?

WorldDomination.fp7

create first table...field defs...a few recursive functions...

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×

×
×
• Create New...