Improved....


Option Explicit

Function HexTri2Dec(s As String)
Dim c As String * 1, bNeg As Boolean
Dim i As Long, f As Double, d As Double, x As Long
s = Trim(s)
If Mid(s, 1, 1) = "-" Then
If Len(s) = 1 Then GoTo badForm
bNeg = True: i = 2
Else
bNeg = False: i = 1
End If
c = ""
On Error Resume Next
For i = i To Len(s)
c = LCase(Mid(s, i, 1))
If c = "." Then Exit For
If "0" <= c And c <= "9" Then x = Asc(c) - 48 _
Else If "a" <= c And c <= "z" Then x = Asc(c) - 87 _
Else: GoTo badForm
d = d * 36 + x
If Err.Number < 0 Then GoTo badNum
Next i
If c = "." Then
f = 1
For i = i + 1 To Len(s)
c = LCase(Mid(s, i, 1))
If "0" <= c And c <= "9" Then x = Asc(c) - 48 _
Else If "a" <= c And c <= "z" Then x = Asc(c) - 87 _
Else: GoTo badForm
f = f * 36
If Err.Number < 0 Then GoTo done
d = d + x / f
Next i
End If

done:
If bNeg Then d = -d
HexTri2Dec = d
Exit Function

badNum:
HexTri2Dec = CVErr(xlErrNum)
Exit Function

badForm:
HexTri2Dec = CVErr(xlErrValue)
End Function


----- original message -----

"Joe User" wrote:

"David" wrote:
I have been given a spreadsheet with transaction
numbers converted into base 36- alpha numeric
- I need it in base 10- number format- I have
approx 30,000 of these!- is ther a formula to
convert from 1 to another?

Do you really mean base 16 -- hexadecimal? Are the digits only 0-9 and A-F?

If so, then use HEX2DEC below. But note the restrictions explained in the
Help page. If they do not work for you, you can modify the UDF below,
changing 36 to 10 and changing the sHetTri string among other cosmetic
changes.

If you truly mean hexatridecimal -- digits 0-9 and A-Z -- use the HexTri2Dec
UDF below.

In either case, if the 30,000 hex-whatever numbers are in a single column,
enter the follow formula, whichever applies, in a parallel column:

=HEX2DEC(A1)

=HexTri2Dec(A1)

If you wish, you can copy the new column, use paste-special-value to replace
the original column, then delete the new column.

UDF....

Option Explicit

Function HexTri2Dec(s As String)
Const sHexTri As String * 36 = _
"0123456789abcdefghijklmnopqrstuvwxyz"
Dim c As String * 1, bNeg As Boolean
Dim i As Long, f As Double, d As Double, x As Long
s = Trim(s)
If Mid(s, 1, 1) = "-" Then
If Len(s) = 1 Then GoTo badForm
bNeg = True: i = 2
Else
bNeg = False: i = 1
End If
c = ""
On Error Resume Next
For i = i To Len(s)
c = LCase(Mid(s, i, 1))
If c = "." Then Exit For
x = InStr(sHexTri, c)
If x = 0 Then GoTo badForm
d = d * 36 + x - 1
If Err.Number < 0 Then GoTo badNum
Next i
If c = "." Then
f = 1
For i = i + 1 To Len(s)
c = LCase(Mid(s, i, 1))
x = InStr(sHexTri, c)
If x = 0 Then GoTo badForm
f = f * 36
If Err.Number < 0 Then GoTo done
d = d + (x - 1) / f
Next i
End If

done:
If bNeg Then d = -d
HexTri2Dec = d
Exit Function

badNum:
HexTri2Dec = CVErr(xlErrNum)
Exit Function

badForm:
HexTri2Dec = CVErr(xlErrValue)
End Function