Ron thank very much. That's what i want to do. the value i put in the
previous posting is just example. You got the right value. So I chould
concat the values and convert. Please tell me waht you did. I should be
able to replicate that.
Well, I used two of my own UDF's in an array formula.
I entered your data in A1:A5.
I used the following array formula:
(To enter an array-formula, after typing or pasting it into the formula bar,
hold down <ctrl><shift> while hitting <enter>. XL will place braces {...}
around the formula)>
=baseconvert(setstring(0,"0.",UPPER(RIGHT(A1:A5,2))),16,10,20)
The RIGHT function pulls out the hex numbers. I have assumed they will be only
two digits. If the number of digits can vary, then this should be modified to
a MID function.
The UPPER because in your example, you used some lower case letters, and my
routine requires upper case letters for bases up to 36.
setstring is a UDF. Its first argument (the 0) represents the number of spaces
between each string. The subsequent arguments represent the strings or
references to concatenate.
baseconvert is a UDF. The arguments are:
the number to be converted
the base of the number being converted
the base of the result
the number of decimal places in the result (optional)
Here are the UDF's:
====================================
Function SetString(SpacesBetween As Integer, _
ParamArray rg() As Variant) As String
'by Ron Rosenfeld
Dim c As Variant
Dim i As Long
For i = 0 To UBound(rg)
Select Case VarType(rg(i))
Case Is = vbArray + vbVariant
For Each c In rg(i)
SetString = SetString & Space(SpacesBetween) & c
Next
Case Is = vbString
SetString = SetString & Space(SpacesBetween) & rg(i)
End Select
Next i
SetString = Trim(SetString)
End Function
----------------------------
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
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, ".") - 2
If LDI = -2 Then LDI = Len(num) - 1
j = LDI
Temp = Replace(num, ".", "")
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) = "."
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
=============================
HTH,
--ron
