Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 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 |
#3
|
|||
|
|||
On Sat, 15 Jan 2005 01:51:35 GMT, Wildman 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 |
#4
|
|||
|
|||
On Sat, 15 Jan 2005 01:51:35 GMT, Wildman 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 |
#5
|
|||
|
|||
On Sat, 15 Jan 2005 01:51:35 GMT, Wildman 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 |
#6
|
|||
|
|||
On Sat, 15 Jan 2005 01:51:35 GMT, Wildman 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 |
#7
|
|||
|
|||
On Sun, 16 Jan 2005 20:50:57 GMT, Wildman 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 |
#8
|
|||
|
|||
On Sun, 16 Jan 2005 17:41:35 -0500, Ron Rosenfeld
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 |
#9
|
|||
|
|||
On Mon, 17 Jan 2005 00:53:35 GMT, Wildman wrote:
On Sun, 16 Jan 2005 17:41:35 -0500, Ron Rosenfeld 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 |
#10
|
|||
|
|||
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 wrote: On Mon, 17 Jan 2005 00:53:35 GMT, Wildman wrote: On Sun, 16 Jan 2005 17:41:35 -0500, Ron Rosenfeld 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 |
#11
|
|||
|
|||
On Mon, 17 Jan 2005 04:59:22 GMT, Wildman 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 |
#12
|
|||
|
|||
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 wrote: =BaseConvert(A1, 32, 10) |
#13
|
|||
|
|||
On Mon, 17 Jan 2005 16:18:51 GMT, Wildman 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 |
#14
|
|||
|
|||
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 wrote: On Mon, 17 Jan 2005 16:18:51 GMT, Wildman 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 |
#15
|
|||
|
|||
On Tue, 18 Jan 2005 01:00:13 GMT, Wildman 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert decimal number to time : convert 1,59 (minutes, dec) to m | Excel Discussion (Misc queries) | |||
How do i convert a number of seconds to a date/time? | Excel Worksheet Functions | |||
Convert data of cells to any type: Number, Date&Time, Text | Excel Discussion (Misc queries) | |||
When we enter a 16 digit number (credit card) the last digit chan. | Excel Discussion (Misc queries) | |||
Convert week number into calendar month? | Excel Worksheet Functions |