formulat to convert base 32 to decimal?

  • Thread starter Thread starter Guest
  • Start date Start date
You can use a UDF: the code below can be used like:

=Base2Dec("V8",32)

to return 1000.

PS - I'm just guessing what the letter coding for base 32 is - an extension
of that used for hexadecimal???

HTH,
Bernie
MS Excel MVP

Public Function Dec2Base(Num As Long, base As Long) As String
'converts a decimal number to the equivalent in the specified base
'(base 2 to base 16). Base needs to be specified as decimal ie
'8 for base 8, 16 for base 16, 2 for base 2 etc
Static Digits As Variant
Dim i As Long, alHolder() As Long, sTemp As String

If IsEmpty(Digits) Then _
Digits = VBA.Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9",
"A", "B", _
"C", "D", "E", "F", "G", "H", "I", "J", "K", "L",
"M", "N", _
"O", "P", "Q", "R", "S", "T", "U", "V")

'check valid base:
If base > 32 Then Dec2Base = "Invalid base used": Exit Function
'fill holder array:
i = 0
Do
ReDim Preserve alHolder(0 To i)
alHolder(i) = Num Mod base
i = i + 1
Num = Num \ base
Loop While Num > 0

'build string result in base:
sTemp = ""
For i = i - 1 To 0 Step -1
sTemp = sTemp & Digits(alHolder(i))
Next i
'output:
Dec2Base = sTemp
End Function

Public Function Base2Dec(Num As String, base As Long) As Variant
'converts a number in the specified base
'(base 2 to base 32) into the equivalent decimal.
'8 for base 8, 16 for base 16, 2 for base 2 etc
Static Digits As Variant
Dim i As Integer
Dim myIndex As Integer
Dim myStr As String

If IsEmpty(Digits) Then _
Digits = VBA.Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9",
"A", "B", _
"C", "D", "E", "F", "G", "H", "I", "J", "K", "L",
"M", "N", _
"O", "P", "Q", "R", "S", "T", "U", "V")

'check valid base:
If base > 32 Then Base2Dec = "Invalid base used": Exit Function
'fill holder array:
Base2Dec = 0

For i = Len(Num) To 1 Step -1
myStr = Mid(Num, i, 1)
myIndex = Application.WorksheetFunction.Match(myStr, Digits) - 1
Base2Dec = Base2Dec + myIndex * base ^ (Len(Num) - i)
Next i

End Function
 
According to Wikipedia, in Base 32, values from 0..31 are represented
with A..Z, 2..7. Unlike in hex, A --> 0. (The numerals 0 and 1 are
not used to prevent confusion with letters O and I.)

This overlong formula converts the value in A1 from Base 32 to Base
10. It assumes the only symbols being used are A..Z, a..z, and 2..7.
Including other symbols will induce a wrong answer.

=IF($A$1="","",SUMPRODUCT(((CODE(MID($A$1,ROW(INDIRECT("1:"&LEN($A
$1))),1))-24) * ( CODE(MID($A$1,ROW(INDIRECT("1:"&LEN($A$1))),1))<58))
+ ((CODE(UPPER(MID($A$1,ROW(INDIRECT("1:"&LEN($A$1))),1)))-65) *
((CODE(UPPER(MID($A$1,ROW(INDIRECT("1:"&LEN($A$1))),1)))>64) )),
POWER(32,LEN($A$1) - ROW(INDIRECT("1:"&LEN($A$1))))))

- David Hilberg
 
FTR, slightly shorter:

=IF(A1="","0",SUMPRODUCT( POWER(32,LEN(A1) -
ROW(INDIRECT("1:"&LEN(A1)))),
(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))) -
24*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<58) -
65*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>64))))

Can be converted to a Hex-to-Decimal formula by changing 32 to 16; 24
to 48; and 65 to 55. (Just for comparison to Excel's built-in HEX2DEC
function, of course.)

- David Hilberg
 
Just 2 cents. Most math programs follow Bernie's example.
0-9, then a-z. This allows programs to work up to base 36 with no problems.

32^^v8

1000
 
Gotcha. Thanks for the info! This version assumes symbols ordered as:
0..9, a..z (or A..Z), and should handle any conversion to base 10 from
bases 11 through 36 (just replace the 32).

=IF(A1="","0",SUMPRODUCT( POWER(32,LEN(A1) -
ROW(INDIRECT("1:"&LEN(A1)))),
(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))) -
48*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<58) -
55*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>64))))

- David Hilberg
 
Back
Top