convert a nine digit number to base 32

anyone know how to change a nine digit number into a base 32 number?

change "347472963" to "abc123"


I have found away to convert a 6 charactor base 32 string to decimal
using a VLOOkup to change the alpha charactor to decimal.
but Im stuck going to other way.

Thanks to any help in advance.

Wildman

Here's some VBA code. The input must be a decimal number. The new base must be
between 2 and 36.

Several routines have been posted in the past by Harlan Grove, Ron Rosenfeld,
and myself, perhaps others. You should find code that will convert a
non-decimal number to some other base, 10 or otherwise.

Google can find the code for you.

But 347472963 is 14B60443 in hex. Conversely, ABC123 is 11256099 in decimal.


Function ConvertBase(ByVal lValue As Variant, iBase As Integer) _
As String
'convert a base-10 number to a new base
'will handle an integer with 15 decimal sDigits, which is the
'limit of precision for a double
Const sDigits = "123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Const MaxLen = 56
Dim IsNeg As Boolean
Dim sNumber As String
Dim p As Integer
Dim iDigit As Integer
Dim PrevValue As Variant

'Trap base value errors
If (iBase > 36) Or (iBase < 2) Then Exit Function

IsNeg = False
If lValue < 0 Then
IsNeg = True
lValue = -lValue
End If

sNumber = String$(MaxLen, "0")
p = MaxLen + 1

Do While lValue > 0
PrevValue = lValue
lValue = Int(lValue / iBase)
iDigit = PrevValue - lValue * iBase
p = p - 1
If iDigit Then Mid$(sNumber, p, 1) = Mid$(sDigits, iDigit, 1)
Loop

If p > MaxLen Then p = p - 1

If IsNeg Then
p = p - 1
Mid$(sNumber, p, 1) = "-"
End If

ConvertBase = Mid$(sNumber, p)
End Function 'ConvertBase


On Sat, 15 Jan 2005 01:51:35 GMT, Wildman <[email protected]> wrote:

>anyone know how to change a nine digit number into a base 32 number?
>
>change "347472963" to "abc123"
>
>
>I have found away to convert a 6 charactor base 32 string to decimal
>using a VLOOkup to change the alpha charactor to decimal.
>but Im stuck going to other way.
>
>Thanks to any help in advance.
>
>Wildman

On Sat, 15 Jan 2005 01:51:35 GMT, Wildman <[email protected]> wrote:

>anyone know how to change a nine digit number into a base 32 number?
>
>change "347472963" to "abc123"
>
>
>I have found away to convert a 6 charactor base 32 string to decimal
>using a VLOOkup to change the alpha charactor to decimal.
>but Im stuck going to other way.
>
>Thanks to any help in advance.
>
>Wildman

Test: Ignore
--ron

On Sat, 15 Jan 2005 01:51:35 GMT, Wildman <[email protected]> wrote:

>anyone know how to change a nine digit number into a base 32 number?
>
>change "347472963" to "abc123"
>
>
>I have found away to convert a 6 charactor base 32 string to decimal
>using a VLOOkup to change the alpha charactor to decimal.
>but Im stuck going to other way.
>
>Thanks to any help in advance.
>
>Wildman

Here is some code that should convert between bases (and will also handle
fractional numbers). But I've used capital letters for up to base 37 and small
letters for higher bases. So 347472963 under this scheme would convert to
ABC123. abc123 would be at least a base 39 number and would be, in base 10:
3335924205

If you have a version of Excel prior to about 2000, you will have to write (or
copy from the MS web site), routines for StrReverse, Split, Join which were in
the later versions of VBA.

Harlan has also written a version that works by string manipulation.


==========================
Function BaseConvert(Num, FromBase As Integer, _
ToBase As Integer, Optional DecPlace As Long) _
As String

'by Ron Rosenfeld

Dim LDI As Integer 'Leading Digit Index
Dim i As Integer, j As Integer
Dim Temp, Temp2
Dim Digits()
Dim r
Dim DecSep As String

DecSep = Application.International(xlDecimalSeparator)

On Error GoTo HANDLER

If FromBase > 62 Or ToBase > 62 _
Or FromBase < 2 Or ToBase < 2 Then
BaseConvert = "Base out of range"
Exit Function
End If

If InStr(1, Num, "E") And FromBase = 10 Then
Num = CDec(Num)
End If

'Convert to Base 10
LDI = InStr(1, Num, DecSep) - 2
If LDI = -2 Then LDI = Len(Num) - 1

j = LDI

Temp = Replace(Num, DecSep, "")
For i = 1 To Len(Temp)
Temp2 = Mid(Temp, i, 1)
Select Case Temp2
Case "A" To "Z"
Temp2 = Asc(Temp2) - 55
Case "a" To "z"
Temp2 = Asc(Temp2) - 61
End Select
If Temp2 >= FromBase Then
BaseConvert = "Invalid Digit"
Exit Function
End If
r = CDec(r + Temp2 * FromBase ^ j)
j = j - 1
Next i

If r <> 0 Then LDI = Fix(CDec(Log(r) / Log(ToBase)))
If r < 1 Then LDI = 0

ReDim Digits(LDI)

For i = UBound(Digits) To 0 Step -1
Digits(i) = Format(Fix(r / ToBase ^ i))
r = CDbl(r - Digits(i) * ToBase ^ i)
Select Case Digits(i)
Case 10 To 35
Digits(i) = Chr(Digits(i) + 55)
Case 36 To 62
Digits(i) = Chr(Digits(i) + 61)
End Select
Next i

Temp = StrReverse(Join(Digits, "")) 'Integer portion
ReDim Digits(DecPlace)

If r <> 0 Then
Digits(0) = DecSep
For i = 1 To UBound(Digits)
Digits(i) = Format(Fix(r / ToBase ^ -i))
r = CDec(r - Digits(i) * ToBase ^ -i)
Select Case Digits(i)
Case 10 To 35
Digits(i) = Chr(Digits(i) + 55)
Case 36 To 62
Digits(i) = Chr(Digits(i) + 61)
End Select
Next i
End If

BaseConvert = Temp & Join(Digits, "")

Exit Function
HANDLER: MsgBox ("Error: " & Err.Number & " " & Err.Description & vbLf & _
"Number being converted: " & Num)

End Function
==================================

--ron
--ron

On Sat, 15 Jan 2005 01:51:35 GMT, Wildman <[email protected]> wrote:

>anyone know how to change a nine digit number into a base 32 number?
>
>change "347472963" to "abc123"
>
>
>I have found away to convert a 6 charactor base 32 string to decimal
>using a VLOOkup to change the alpha charactor to decimal.
>but Im stuck going to other way.
>
>Thanks to any help in advance.
>
>Wildman

Here is some code that should convert between bases (and will also handle
fractional numbers). But I've used capital letters for up to base 37 and small
letters for higher bases. So 347472963 under this scheme would convert to
ABC123. abc123 would be at least a base 39 number and would be, in base 10:
3335924205

If you have a version of Excel prior to about 2000, you will have to write (or
copy from the MS web site), routines for StrReverse, Split, Join which were in
the later versions of VBA.

Harlan has also written a version that works by string manipulation.


==========================
Function BaseConvert(Num, FromBase As Integer, _
ToBase As Integer, Optional DecPlace As Long) _
As String

'by Ron Rosenfeld

Dim LDI As Integer 'Leading Digit Index
Dim i As Integer, j As Integer
Dim Temp, Temp2
Dim Digits()
Dim r
Dim DecSep As String

DecSep = Application.International(xlDecimalSeparator)

On Error GoTo HANDLER

If FromBase > 62 Or ToBase > 62 _
Or FromBase < 2 Or ToBase < 2 Then
BaseConvert = "Base out of range"
Exit Function
End If

If InStr(1, Num, "E") And FromBase = 10 Then
Num = CDec(Num)
End If

'Convert to Base 10
LDI = InStr(1, Num, DecSep) - 2
If LDI = -2 Then LDI = Len(Num) - 1

j = LDI

Temp = Replace(Num, DecSep, "")
For i = 1 To Len(Temp)
Temp2 = Mid(Temp, i, 1)
Select Case Temp2
Case "A" To "Z"
Temp2 = Asc(Temp2) - 55
Case "a" To "z"
Temp2 = Asc(Temp2) - 61
End Select
If Temp2 >= FromBase Then
BaseConvert = "Invalid Digit"
Exit Function
End If
r = CDec(r + Temp2 * FromBase ^ j)
j = j - 1
Next i

If r <> 0 Then LDI = Fix(CDec(Log(r) / Log(ToBase)))
If r < 1 Then LDI = 0

ReDim Digits(LDI)

For i = UBound(Digits) To 0 Step -1
Digits(i) = Format(Fix(r / ToBase ^ i))
r = CDbl(r - Digits(i) * ToBase ^ i)
Select Case Digits(i)
Case 10 To 35
Digits(i) = Chr(Digits(i) + 55)
Case 36 To 62
Digits(i) = Chr(Digits(i) + 61)
End Select
Next i

Temp = StrReverse(Join(Digits, "")) 'Integer portion
ReDim Digits(DecPlace)

If r <> 0 Then
Digits(0) = DecSep
For i = 1 To UBound(Digits)
Digits(i) = Format(Fix(r / ToBase ^ -i))
r = CDec(r - Digits(i) * ToBase ^ -i)
Select Case Digits(i)
Case 10 To 35
Digits(i) = Chr(Digits(i) + 55)
Case 36 To 62
Digits(i) = Chr(Digits(i) + 61)
End Select
Next i
End If

BaseConvert = Temp & Join(Digits, "")

Exit Function
HANDLER: MsgBox ("Error: " & Err.Number & " " & Err.Description & vbLf & _
"Number being converted: " & Num)

End Function
==================================

--ron
--ron

On Sat, 15 Jan 2005 01:51:35 GMT, Wildman <[email protected]> wrote:

>anyone know how to change a nine digit number into a base 32 number?
>
>change "347472963" to "abc123"
>
>
>I have found away to convert a 6 charactor base 32 string to decimal
>using a VLOOkup to change the alpha charactor to decimal.
>but Im stuck going to other way.
>
>Thanks to any help in advance.
>
>Wildman



I'm not sure how to use the visual basic
code. Could someone "splane" to me
what to do with it?

Thanks again

Wildman

On Sun, 16 Jan 2005 20:50:57 GMT, Wildman <[email protected]> wrote:


>
>
>
>I'm not sure how to use the visual basic
>code. Could someone "splane" to me
>what to do with it?
>
>Thanks again
>
>Wildman

Open Excel.

<alt><F11> opens the VB Editor.

Ensure your project is highlighted in the Project Explorer window.

Then (form the VBE main menu), Insert/Module. Paste the code into the window
that opens.

If you are using my routine, you have just installed a UDF (user defined
function).

You can use this function like any other in your worksheet. For example, with
your number in A1, in some cell type

=BaseConvert(A1, 10, 32) and the number in A1 will be shown to Base 32 in this
cell.

You may also use cell references in place of the 10 and the 32 for more
flexibility. If you have a fractional portion of a number to be converted,
then you can use the optional DecPlace argument to indicate the number of
places.


--ron

On Sun, 16 Jan 2005 17:41:35 -0500, Ron Rosenfeld
<[email protected]> wrote:

>=BaseConvert(A1, 10, 32



Hmm mm.... I just get a #NAME? error

cell A1 has 123456 and cell B1 has =BaseConvert(A1, 10, 32)

outcome in B1 is #NAME?


Excel 2003, security medium, OK ed macro to run on startup.

allmost there ;-)

Wildman

On Mon, 17 Jan 2005 00:53:35 GMT, Wildman <[email protected]> wrote:

>On Sun, 16 Jan 2005 17:41:35 -0500, Ron Rosenfeld
><[email protected]> wrote:
>
>>=BaseConvert(A1, 10, 32
>
>
>
>Hmm mm.... I just get a #NAME? error
>
>cell A1 has 123456 and cell B1 has =BaseConvert(A1, 10, 32)
>
>outcome in B1 is #NAME?
>
>
>Excel 2003, security medium, OK ed macro to run on startup.
>
>allmost there ;-)
>
>Wildman
>

I guess you don't have the BaseConvert routine in the correct spot.

After you open the VB Editor, in the Project Explorer window, you should see a
bunch of unexpanded entries, along with an expanded entry that looks something
like:

VBAProject (Book2)
Microsoft Excel Objects
Sheet1 (Sheet1)
ThisWorkbook

(Book2) might be, instead, the name of your workbook; same with (Sheet1) might
be the name of your worksheet.

After you select Insert/Module, the same project should look like:

VBAProject (Book2)
Microsoft Excel Objects
Sheet1 (Sheet1)
ThisWorkbook
Modules
Module1

Double click on Module1 to ensure that the correct window is open. Then paste
in the BaseConvert code from my previous post.


--ron

Well Ron......

You DA Man!

and I thank you for your help.

Do you have any VBA for base 32 to Decimal?
The formulas I wrote to do that seem quite
archaic compared to the VBA.

Thanks again
Wildman


On Sun, 16 Jan 2005 21:23:54 -0500, Ron Rosenfeld
<[email protected]> wrote:

>On Mon, 17 Jan 2005 00:53:35 GMT, Wildman <[email protected]> wrote:
>
>>On Sun, 16 Jan 2005 17:41:35 -0500, Ron Rosenfeld
>><[email protected]> wrote:
>>
>>>=BaseConvert(A1, 10, 32
>>
>>
>>
>>Hmm mm.... I just get a #NAME? error
>>
>>cell A1 has 123456 and cell B1 has =BaseConvert(A1, 10, 32)
>>
>>outcome in B1 is #NAME?
>>
>>
>>Excel 2003, security medium, OK ed macro to run on startup.
>>
>>allmost there ;-)
>>
>>Wildman
>>
>
>I guess you don't have the BaseConvert routine in the correct spot.
>
>After you open the VB Editor, in the Project Explorer window, you should see a
>bunch of unexpanded entries, along with an expanded entry that looks something
>like:
>
>VBAProject (Book2)
> Microsoft Excel Objects
> Sheet1 (Sheet1)
> ThisWorkbook
>
>(Book2) might be, instead, the name of your workbook; same with (Sheet1) might
>be the name of your worksheet.
>
>After you select Insert/Module, the same project should look like:
>
>VBAProject (Book2)
> Microsoft Excel Objects
> Sheet1 (Sheet1)
> ThisWorkbook
> Modules
> Module1
>
>Double click on Module1 to ensure that the correct window is open. Then paste
>in the BaseConvert code from my previous post.
>
>
>--ron

On Mon, 17 Jan 2005 04:59:22 GMT, Wildman <[email protected]> wrote:

>Well Ron......
>
>You DA Man!
>
>and I thank you for your help.
>
>Do you have any VBA for base 32 to Decimal?
>The formulas I wrote to do that seem quite
>archaic compared to the VBA.
>
>Thanks again
>Wildman

Wildman,

Look closely at the code for my BaseConvert Routine:

Function BaseConvert(Num, FromBase As Integer, _
ToBase As Integer, Optional DecPlace As Long) _
As String

Num is the number you are converting

FromBase is the base from which you are converting (or the base of Num)

ToBase is the base to which you wish to convert.

DecPlace is an optional argument to use if you want a fractional part in the
result.

So you would just rewrite your worksheet function as:

=BaseConvert(A1, 32, 10)

Note that you could have the bases in cell references also, for flexibility:

A1: number to be converted
A2: Base of number in A1
A3: Base of result

=BaseConvert(A1, A2, A3)

and so forth.


--ron

I tryed the =BaseConvert(A1, 32, 10)
but the outcome shows "invalid digit"
any time an alpha character is used.
I works fine on all numerical numbers.





On Mon, 17 Jan 2005 04:17:01 -0500, Ron Rosenfeld
<[email protected]> wrote:

>=BaseConvert(A1, 32, 10)

On Mon, 17 Jan 2005 16:18:51 GMT, Wildman <[email protected]> wrote:

>I tryed the =BaseConvert(A1, 32, 10)
>but the outcome shows "invalid digit"
>any time an alpha character is used.
>I works fine on all numerical numbers.

What exactly is the formula and values that gave you that result?

The formula will show "invalid digit" if any character is not defined in the
base from which you are converting.


--ron

I got it now...
(Did not use Cap Letters)

Man this is Awsome!

Thanks for all you help!


Wildman





On Mon, 17 Jan 2005 11:52:35 -0500, Ron Rosenfeld
<[email protected]> wrote:

>On Mon, 17 Jan 2005 16:18:51 GMT, Wildman <[email protected]> wrote:
>
>>I tryed the =BaseConvert(A1, 32, 10)
>>but the outcome shows "invalid digit"
>>any time an alpha character is used.
>>I works fine on all numerical numbers.
>
>What exactly is the formula and values that gave you that result?
>
>The formula will show "invalid digit" if any character is not defined in the
>base from which you are converting.
>
>
>--ron

On Tue, 18 Jan 2005 01:00:13 GMT, Wildman <[email protected]> wrote:

>I got it now...
>(Did not use Cap Letters)
>
>Man this is Awsome!
>
>Thanks for all you help!

You're welcome. I'm glad you got it working for you.


--ron