is there a Base 26 / Radix 26 A..Z functions in the libs?

[jamie]

I am an advocate of using what is already in the libs instead of re-inventing the wheel and adding bloat to the code.

I need to Decode a string input like this.

'A..Z' or any combination like 'AA', 'BB' etc.

works like HEX digits only each digit is worth 26.

I am using this for the RULERS on the side of a Document sheet, and I know there is some spread sheet code hanging around here that does encode and decode these columns.

 For now I am just looking to convert to an integer;

 No need for anyone to write one for me, I can do that myself but would love to reuse code.

Jamie

[dsiders]

I am an advocate of using what is already in the libs instead of re-inventing the wheel and adding bloat to the code.

I need to Decode a string input like this.

'A..Z' or any combination like 'AA', 'BB' etc.

works like HEX digits only each digit is worth 26.

I am using this for the RULERS on the side of a Document sheet, and I know there is some spread sheet code hanging around here that does encode and decode these columns.

 For now I am just looking to convert to an integer;

 No need for anyone to write one for me, I can do that myself but would love to reuse code.

Jamie

Haven't used this myself, but...

https://wiki.freepascal.org/Base_converting

[jamie]

Nice looking code there, but it looks like not exactly what I was looking for.

Example:

    "A" = 0; "AA" = 26

So:

 "B" = 1 and "BA" = 27

 
I can't believe someone hasn't come up with this yet for a common base function.

Jamie

[Thausand]

I can't believe someone hasn't come up with this yet for a common base function.

I have think this not present because is use case special.

When column indicate can make 1:1 map-look-table. When want practice use then why is limit 26 ? there is also exist lowcase and upcase character and number and more other character then can have example base 36 when have include number.

[jamie]

I had to settle for this.

Code: Pascal  [Select][+][-]

1. function DecodeGridLetterValue(aValue: ansistring): int64;
2. var
3.   I: integer;
4. begin
5.   aValue := UpperCase(aValue);
6.   Result := 0;
7.   for I := 1 to Length(AValue) do
8.   begin
9.     Result := Result * 26 + (Ord(AValue[I]) - Ord('A'));
10.   end;
11. end;
12.  
13. function EncodeGridLetterValue(aValue: int64): ansistring;
14. var
15.   Balance: integer;
16. begin
17.   repeat
18.     Balance := Avalue mod 26;
19.     Result := Result.InSert(0, char(Ord('A') + Balance));
20.     AValue := AValue div 26;
21.   until AValue = 0;
22. end;                                                                  
23.  
24.  

So I can get A..Z, but for multiples I had to make sure I didn't have a 0 (A) value at the start.
"A" = 0
"Z" = 25
"BA" = 26

And so on. With this I can Encode and decode the RULER marks and make an indexable lookup Ruler.

[Zvoni]

Code: Pascal  [Select][+][-]

1. function DecodeGridLetterValue(aValue: ansistring): int64;
2. var
3.   I: integer;
4. begin
5.   aValue := UpperCase(aValue);
6.   Result := 0;
7.   for I := 1 to Length(AValue) do
8.   begin
9.     Result := Result * 26 + (Ord(AValue[I]) - Ord('A'))+1;  //Add 1 to get original Excel-Algorithm
10.   end;
11.   Result:=Result-1; //Decrease by one at the end to get 0-based
12. end;

A returns 0
Z returns 25
AA returns 26

FWIW, i'd use UInt64 as Result-Type, since you are not expecting negative Values

UNTESTED!!!

Code: Pascal  [Select][+][-]

1. function EncodeGridLetterValue(aValue: int64): ansistring;
2. var
3.   Balance: integer;
4. begin
5.   //We have to offset --> you pass "25" you expect "Z", you pass "26" you expect "AA"
6.   AValue:=AValue+1;
7.   //This is the Algorithm to return the Original Excel-lettering 1-based
8.   repeat
9.     Balance := (Avalue-1) mod 26;
10.     Result := Result.InSert(0, char(Ord('A') + Balance));
11.     AValue := (AValue-Balance) div 26;
12.   until AValue = 0;
13. end;              

Tested both.
Works

[Thaddy]

@Zvoni

Based on your code, a bit optimized, and reliance on sysutils removed:

Code: Pascal  [Select][+][-]

1. {$mode objfpc}{$H+}
2. const A = 65;
3.  
4. function DecodeGridLetterValue(aValue: ansistring): Qword;
5. var
6.   c:AnsiChar;
7. begin
8.   aValue := UpCase(aValue);
9.   Result := 0;
10.   for C in AValue do
11.     Result := Result * 26 + (Ord(C) - A)+1;  //Add 1 to get original Excel-Algorithm
12.   Dec(Result); //Decrease by one at the end to get 0-based
13. end;
14.  
15. function EncodeGridLetterValue(aValue: Qword): ansistring;
16. var
17.   Balance: integer;
18. begin
19.   Result := '';// initialize
20.   //We have to offset --> you pass "25" you expect "Z", you pass "26" you expect "AA"
21.   AValue:=AValue+1;
22.   //This is the Algorithm to return the Original Excel-lettering 1-based
23.   repeat
24.     Balance := Pred(Avalue) mod 26;
25.     Result := char(A + Balance) + Result; // no insert.
26.     AValue := (AValue-Balance) div 26;
27.   until AValue = 0;
28. end;      
29.  
30. begin
31.   writeln(DecodeGridLetterValue('A'));
32.   writeln(DecodeGridLetterValue('AA'));
33.   writeln(EncodeGridLetterValue(26));
34.   writeln(EncodeGridLetterValue(25));
35.   writeln(EncodeGridLetterValue(0));  
36. end.  

     
Results are the same.
Slightly edited.

[Zvoni]

Thaddy,
very nice.
Yeah, that "Insert" bugged me, too, and in my tests in VBA i actually did use "reverse" concatenation.
But i wanted to stay as close as possible to Jamie's original code, so... *shrug*

I was even thinking introducing a Flag as Function-Argument, something like "... var ZeroBased:Integer=1..."

along the lines of

Code: Pascal  [Select][+][-]

1. {$mode objfpc}{$H+}
2. function DecodeGridLetterValue(aValue: ansistring; var ZeroBased:Integer=0): Qword;
3. var
4.   c:AnsiChar;
5. begin
6.   aValue := UpCase(aValue);// from system
7.   Result := 0;
8.   for C in AValue do
9.     Result := Result * 26 + (Ord(C) - Ord('A'))+1;  //Add 1 to get original Excel-Algorithm
10.   If ZeroBased<>0 Then ZeroBased:=1;
11.   Result:=Result-ZeroBased;
12.   //Dec(Result); //Decrease by one at the end to get 0-based
13. end;
14.  
15. function EncodeGridLetterValue(aValue: Qword; var ZeroBased:Integer=0): ansistring;
16. var
17.   Balance: integer;
18. begin
19.   Result := '';// initialize
20.   If ZeroBased<>0 Then ZeroBased:=1;
21.   //We have to offset --> you pass "25" you expect "Z", you pass "26" you expect "AA"
22.   AValue:=AValue+ZeroBased;
23.   //This is the Algorithm to return the Original Excel-lettering 1-based
24.   repeat
25.     Balance := (Avalue-1) mod 26;
26.     Result := char(Ord('A') + Balance) + Result; // do not use insert. Reverse the order and concat.
27.     AValue := (AValue-Balance) div 26;
28.   until AValue = 0;
29. end;      

That way he could have both worlds

[Thaddy]

Yes, that is a good idea.
(Btw, tested with longer strings too: works verifiably in all cases probed within the limits.)

Suggest to make ZeroBased Boolean (although that restricts to 1 based)

[Zvoni]

Yes, that is a good idea.
(Btw, tested with longer strings too: works verifiably in all cases probed within the limits.)

Suggest to make ZeroBased Boolean (although that restricts to 1 based)

Yeah, but you'd have to convert the boolean back to an integer, so idk what gain you would have.
OTOH it would be easy with a constant lookup-array
Offset:Array[Boolean] Of Integer=(0,1);
For the life of me, i severly dislike "... Ord(SomeBoolean)"

[Bart]

I have some IntToStrBase() and vice versa routines in my fsiconv.pp unit.

Feel free to use or simply discard it.

Bart

[dseligo]

There are also some functions in fpSpreadsheet that do something like that (unit fpsUtils.pas), i.e. ParseCellColString and GetColString.

[jamie]

I am happy with what I posted with the exception of initializing the return string, that I forgot to do and saw the effects in real code use.

thanks for taking interest.

Jamie

[Zvoni]

Would you believe it, that i just needed this myself?
Talk about "recycling".....