Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > formulat to convert base 32 to decimal?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 1
Default formulat to convert base 32 to decimal?
Is there an excel formula to convert base 32 (6digit) to decimal?
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 5,441
Default formulat to convert base 32 to decimal?
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



"TMG005" wrote in message
...
Is there an excel formula to convert base 32 (6digit) to decimal?


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 21
Default formulat to convert base 32 to decimal?
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(INDI RECT("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
-------------------------
Happy Bastille Day


On Jul 13, 9:07 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
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

"TMG005" wrote in message

...

Is there an excel formula to convert base 32 (6digit) to decimal?


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 21
Default formulat to convert base 32 to decimal?
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

On Jul 14, 8:09 pm, D Hilberg wrote:
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(INDI RECT("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
-------------------------
Happy Bastille Day

On Jul 13, 9:07 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:

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

"TMG005" wrote in message

...

Is there an excel formula to convert base 32 (6digit) to decimal?

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 947
Default formulat to convert base 32 to decimal?
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

--
Dana DeLouis


"D Hilberg" wrote in message
ups.com...
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

On Jul 14, 8:09 pm, D Hilberg wrote:
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(INDI RECT("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
-------------------------
Happy Bastille Day

On Jul 13, 9:07 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:

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

"TMG005" wrote in message

...

Is there an excel formula to convert base 32 (6digit) to decimal?



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 21
Default formulat to convert base 32 to decimal?
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


On Jul 15, 4:36 pm, "Dana DeLouis" wrote:
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

--
Dana DeLouis

"D Hilberg" wrote in message

ups.com...

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

On Jul 14, 8:09 pm, D Hilberg wrote:
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(INDI RECT("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
-------------------------
Happy Bastille Day

On Jul 13, 9:07 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:

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

"TMG005" wrote in message

...

Is there an excel formula to convert base 32 (6digit) to decimal?


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Translation Formulat Entry Rob Excel Worksheet Functions 0 June 6th 07 02:54 PM
convert decimal number to time : convert 1,59 (minutes, dec) to m agenda9533 Excel Discussion (Misc queries) 8 January 20th 05 10:24 PM
convert a nine digit number to base 32 Wildman Excel Worksheet Functions 14 January 18th 05 01:21 AM
how do I convert numbers to a different base in Excel? henry Excel Worksheet Functions 1 January 14th 05 02:07 PM
Convert Works 6.0 data base to Excel? CarolER New Users to Excel 1 January 8th 05 05:35 PM


All times are GMT +1. The time now is 03:05 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement