Excel custom bases decode 2

[Brianfree]

Hi, I have a 18 digit value which I have custom encoded via php with base33 (123456789ABCDEFGHJKLMNPQRSTUVWXYZ) this removes o, 0 & I

I need to decode it in excel..
First I need to separate in to 3 blocks of 6 which is easy, im just struggling to base33 decode back into the original values?

Please can anyone assist?

Many thanks

Brian

 

[vgulielmus]

First time I wrote 10,11

My respects,
Vilhelm-Ion Praisach
Resita, Romania

 

[SkipVought]

Ah, then I feel that I am in good company, however impatient.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[zelgar]

Another way to get things to work without using VB is the following:
1. Create an array of your table (like vgulielmus did on April 16) and name it (e.g., BASE33) NOTE: Since the values you'll be looking up is text & numbers, the numbers need to be entered as TEXT. Also, based on Brianfree post on April 16 showing examples, you'll also need to include 0 with a value of O in the table.
2. Next, since the value we're converting can be up to 18 digits long (but the example only had 9 digits) we need to add 0 preceeding the original value to get up to 18 digits. Assuming the information was provided in in Cell A 43, In Cell B 43 put the following code
=IF(LEN(A43<18),CONCATENATE(REPT(0,18-LEN(A43)),A43),A43)
3. Because there's a limitation on the number of characters in a cell, we need to break up the conversions into 2 cells (e.g. Cell C 43 and D 43) with the following code
=VLOOKUP(MID(B43,1,1),BASE33,2)*33^17+VLOOKUP(MID(B43,2,1),BASE33,2)*33^16+VLOOKUP(MID(B43,3,1),BASE33,2)*33^15+VLOOKUP(MID(B43,4,1),BASE33,2)*33^14+VLOOKUP(MID(B43,5,1),BASE33,2)*33^13+VLOOKUP(MID(B43,6,1),BASE33,2)*33^12+VLOOKUP(MID(B43,7,1),BASE33,2)*33^11+VLOOKUP(MID(B43,8,1),BASE33,2)*33^10+VLOOKUP(MID(B43,9,1),BASE33,2)*33^9
=VLOOKUP(MID(B43,10,1),BASE33,2)*33^8+VLOOKUP(MID(B43,11,1),BASE33,2)*33^7+VLOOKUP(MID(B43,12,1),BASE33,2)*33^6+VLOOKUP(MID(B43,13,1),BASE33,2)*33^5+VLOOKUP(MID(B43,14,1),BASE33,2)*33^4+VLOOKUP(MID(B43,15,1),BASE33,2)*33^3+VLOOKUP(MID(B43,16,1),BASE33,2)*33^2+VLOOKUP(MID(B43,17,1),BASE33,2)*33^1+VLOOKUP(MID(B43,18,1),BASE33,2)
4. In Cell E 43 add Cells C & D together to get the converted value
= C43 + D43

 

[mscallisto]

I do like zelgar's approach if only you can put it in a loop varying the lookup start point and the power raise.
is that possible?

 

[strongm]

Here's a fractionally different approach:

[blue]Option Explicit

Private Const Base= "123456789ABCDEFGHJKLMNPQRSTUVWXYZ" [green]' Just to be explicit[/green] 

Private Function test2(strCode As String) As String
    Dim lp As Long
    Dim runningtotal As Long
    Dim test As Long
    
    For lp = 1 To Len(strCode)
        test = InStr(Base, Mid(strCode, lp, 1))
        If test > 0 Then runningtotal = runningtotal + (test - 1) * 33 ^ (Len(strCode) - lp)
    Next

    test2 = Right("000000000" & runningtotal, 9)
End Function[/blue]

 

[SkipVought]

Using the FORMUAL approch...

[pre]
8 7 6 5 4 3 2 1 0 SUM
000008CHG 0 0 0 0 0 251559 11979 528 15 264,081

[/pre]

the formula
[tt]
B2 =IFERROR(VLOOKUP(MID($A2,LEN($A2)-B$1,1),BASE33,2)*33^B$1,0)
[/tt]

Expand powers accordingly

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[vgulielmus]

Splendid improvements for regular numbers.

I have converted a 27 digit numeric value to 18 digits by using a base 33 encode.

I don't get more than 15 digits accuracy only with numeric data types using =33^17 formula in a cell, while in VBA I get numeric overflow with integers or again lack of accuracy with nonintegers numeric data types.
This is the reason I replaced the test procedure with test2. This part is the multiplying algorithm (similar with the one learned in the school):

    cx3 = ""
    For lnj = Len(cx2) To 1 Step -1
        cd2 = Mid(cx2, lnj, 1) 
        ncur = 33 * Val(cd2) + nrem 
        nrem = ncur \ 10 
        cx3 = ncur Mod 10 & cx3 
    Next
    cx2 = nrem & cx3

My respects,
Vilhelm-Ion Praisach
Resita, Romania

 

[zelgar]

If you were willing to change everything to HEX (Base 16), Excel already has a built-in conversion functions HEX2DEC to go from hexadecimal to Decimal & DEC2HEX to from deciminal number to hexadecimal. This is good for number between -549,755,813,888 and 549,755,813,887.

 

[strongm]

> change everything to HEX

1) Not much good for base33 though ...
2) 549,755,813,887 - so 12 digits accuracy at best, not 15.

Although to be fair, the actual requirement currently seems to be 9 digits

 

[SkipVought]

In fact using the formula approch, you could generalize the sheet for ANY BASE by storing the Current Base value in A1 =COUNTA(BASE33)/2, NAMING cell A1 as CurrentBASE and modifying the foumula...
[tt]
B2: =IFERROR(VLOOKUP(MID($A2,LEN($A2)-B$1,1),BASE33,2)*[highlight #FCE94F]CurrentBASE[/highlight]^B$1,0)[/tt]

Of course the BASE33 range would need to be revised accordingly.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[strongm]

Yep, a similar minor change works for my function as well (which is just as well, because that's how it was designed ...):

If test > 0 Then runningtotal = runningtotal + (test - 1) * Len(Base) ^ (Len(strCode) - lp)

 

[Brianfree]

Hi, thank you all for your replys - have got lots of examples to play with!

many thanks

Brian