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

 

[strongm]

This doesn't make sense.

Can you please clarify what you mean by custom encoding with base33? I'm familiar with base64 encoding, but not base33, and you state it is a 'custom encoding'. However, without any information whatsoever about how this encoding is done it is impossible to suggest how it might be decoded.

 

[Brianfree]

Hi, thanks for replying.. I have converted a 27 digit numeric value to 18 digits by using a base 33 encode. by using A-Z (excluding I & O) and 1-9 (excluding 0)

Hope this helps??

many thanks

Brian

 

[strongm]

Nope, not really. I am sure it is crystal clear to you, but not to me. About the only bit I am happy with is that you are calling it 'base 33' because there are 33 characters in your encoding string.

Beyond that there is no information as to how this is actually used to reduce your 27 digits to 18. Since we don't know that we can't suggest how to reverse it.

 

[Brianfree]

Hi, give me a few mins and I will detail how I do it.

Thanks for you help so far!

Brian

 

[vgulielmus]

IF 1->0...9->8 A->9...H->16 J->17...N->21 P->22..Z->33 then please try :

Sub test()
Dim cx As String, nx As Integer, npow As Integer, lni As Integer
cx = ActiveSheet.Cells(1, 1)
nx = 0
npow = 0
For lni = 1 To Len(cx)
    cd = UCase(Mid(cx, lni, 1))
    If cd >= "1" And cd <= "9" Then
        nx = nx * 33 ^ npow + Asc(cd) - Asc("1")
    ElseIf cd >= "A" And cd <= "H" Then
        nx = nx * 33 ^ npow + 9 + Asc(cd) - Asc("A")
    ElseIf cd >= "J" And cd <= "N" Then
        nx = nx * 33 ^ npow + 17 + Asc(cd) - Asc("J")
    ElseIf cd >= "P" And cd <= "Z" Then
        nx = nx * 33 ^ npow + 22 + Asc(cd) - Asc("P")
    End If
    npow = npow + 1
Next
MsgBox (nx)
End Sub

My respects,
Vilhelm-Ion Praisach
Resita, Romania

 

[Brianfree]

Hi, thanks for this. I have tried this - not entirely sure what its doing but for single values up to 3 digits it is returning a value via a msgbox.

Just need to get my head around how its working.

Was starting to look at some kind of formula like ("123456789ABCDEFGHJKLMNPQRSTUVWXYZ",MOD(ROW()-4,33)+1,1) to convert??

Many thanks

Brian

 

[Brianfree]

Hi, still struggling on this, have found an old post of mine where I was doing something like...

=SUM((CODE(MID(A1,{5,4,3,2,1},1))-65)*10^{-1,0,1,2,3})

Not sure if this can be adapted???

Many thanks

Brian

 

[vgulielmus]

The previous code works only for small numbers, not for numbers with 18 or 27 digits.
Enter your coded number in A1, run the following macro and read the result from B1.

Sub test2()
Dim cx As String, cx2 As String, lnj As Integer, lni As Integer, ndigit As Integer, cd As String, cd2 As String, nrem As Integer, ncur As Integer, cx3 As String
cx = ActiveSheet.Cells(1, 1).Value

cx2 = ""
For lni = 1 To Len(cx)
    cd = UCase(Mid(cx, lni, 1))
    If cd >= "1" And cd <= "9" Then
        ndigit = Asc(cd) - Asc("1")
    ElseIf cd >= "A" And cd <= "H" Then
        ndigit = 9 + Asc(cd) - Asc("A")
    ElseIf cd >= "J" And cd <= "N" Then
        ndigit = 17 + Asc(cd) - Asc("J")
    ElseIf cd >= "P" And cd <= "Z" Then
        ndigit = 22 + Asc(cd) - Asc("P")
    End If
    nrem = ndigit
    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
Next
ActiveSheet.Cells(1, 2).Value = "'" & cx2
End Sub

My respects,
Vilhelm-Ion Praisach
Resita, Romania

 

[Brianfree]

Hi, perfect!!!

So...
000008CHG decodes to 000264081
0003QDMRH decodes to 106000801
0003KBND2 decodes to 100000000

Which is all correct.

If I have an excel spread sheet, as I type in a number or scan it in via barcode reader, could it do this on the fly for each one entered?

Many thanks

Brian

 

[SkipVought]

call the test2 procedure from the Worksheet_Change event...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        test2
    End If
End Sub

Skip,

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

 

[Brianfree]

Hi, sorry for being a pain...

What I meant was if I have data like

Value Decode
000008CHG
0003QDMRH
0003KBND2
etc...

As I enter a new value it would appear in the next column and repeat each time I enter a new one?

many thanks

Brian

 

[SkipVought]

Just make test2 a Function(cx as string) in a module, rather than a Sub and enter it in the adhacent column, as you would any other spreadsheet function. Remember to assign "'" & cx2 to the Function name.

Function test2(cx As String)
Dim cx2 As String, lnj As Integer, lni As Integer, ndigit As Integer, cd As String, cd2 As String, nrem As Integer, ncur As Integer, cx3 As String
[s]cx = ActiveSheet.Cells(1, 1).Value[/s]
'.......
[s]ActiveSheet.Cells(1, 2).Value = "'" & cx2[/s]
test2 = "'" & cx2
End Function

Skip,

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

 

[vgulielmus]

Alternative solution.
Formula for B1 =test2(A1)
Formula for B2 =test2(A2)
and so on.

Function test2(rng As Range)
Dim cx As String, cx2 As String, lnj As Integer, lni As Integer, ndigit As Integer, cd As String, cd2 As String, nrem As Integer, ncur As Integer, cx3 As String
cx = rng.Cells(1, 1).Value

cx2 = ""
For lni = 1 To Len(cx)
    cd = UCase(Mid(cx, lni, 1))
    If cd >= "1" And cd <= "9" Then
        ndigit = Asc(cd) - Asc("1")
    ElseIf cd >= "A" And cd <= "H" Then
        ndigit = 9 + Asc(cd) - Asc("A")
    ElseIf cd >= "J" And cd <= "N" Then
        ndigit = 17 + Asc(cd) - Asc("J")
    ElseIf cd >= "P" And cd <= "Z" Then
        ndigit = 22 + Asc(cd) - Asc("P")
    End If
    nrem = ndigit
    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
Next
test2 = "'" & cx2
End Function

My respects,
Vilhelm-Ion Praisach
Resita, Romania

 

[Andrzejek]

It is the same logic (I hope), just easier to read:

Select Case cd
    Case "1" To "9"
        ndigit = Asc(cd) - Asc("1")
    Case "A" To "H"
        ndigit = 9 + Asc(cd) - Asc("A")
    Case "J" To "N"
        ndigit = 17 + Asc(cd) - Asc("J")
    Case "P" To "Z"
        ndigit = 22 + Asc(cd) - Asc("P")
End Select

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

or this

        Select Case cd
            Case Is < "1"
                ndigit = 1
            Case Is < "A"
                ndigit = 0
            Case Is < "I"
                ndigit = -7
            Case Is < "O"
                ndigit = -8
            Case Else
                ndigit = -9
        End Select
        ndigit = ndigit + Asc(cd) - Asc("1")

Skip,

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

 

[SkipVought]

I should have had...

        Select Case cd
            Case "0"
                ndigit = 1
            Case "1" To "9"
                ndigit = 0
            Case "A" To "H"
                ndigit = -7
            Case "J" To "N"
                ndigit = -8
            Case "P" To "Z"
                ndigit = -9
        End Select
        ndigit = ndigit + Asc(cd) - Asc("1")

Skip,

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

 

[vgulielmus]

I assumed your custom base33 encodes like this:
[pre]number endcoded
0 1
1 2
2 3
3 4
4 5
5 6
6 7
7 8
8 9
9 A
10 B
11 C
12 D
13 E
14 F
15 G
16 H
17 J
18 K
19 L
20 M
21 N
22 P
23 Q
24 R
25 S
26 T
27 U
28 V
29 W
30 X
31 Y
32 Z

then
33 21
34 22
35 23
and so on
[/pre]
Examples
1) 372 will be encoded "CA" because
372 : 33 = 11 r 9
11 : 33 = 0 r 11
Concatenating the remainders from bottom to up and converting them => C(=11)A(=9)

2) 2189 will be encoded "31C" because
2189 : 33 = 66 r 11
66 : 33 = 2 r 0
2 : 33 = 0 r 2
Concatenating the remainders from bottom to up and converting them => 3(=2)1(=0)C(=11)

3) The encoded number CA represent the value of 372 because
"C"*33^1+"A"*33^0=11*33+9*1=363+9=372

4) The encoded number 31C represent the value of 2189 because
"3"*33^2+"1"*33^1+"C"*33^0=2*1089+0*33+11=2178+11=2189

My respects,
Vilhelm-Ion Praisach
Resita, Romania

 

[SkipVought]

[pre]
then
33 21 11
34 22 12
35 23 13
and so on
[/pre]

Skip,

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

 

[SkipVought]

sorry Vilhelm, you are correct as 1 is 0 value.

Skip,

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