cg said:

Dirk,

Worked like a charm - THANKS A MILLION!

You're welcome. I had written that function to answer a newsgroup question

years ago. Way back in my mainframe-programming days I had to deal with

zoned decimal numbers fairly often.

Can you tell me how to input a decimal? It appears that the decimal comes

before the last two digits, so:

00000015293F should be $1,539.36

How about this untested modification of the original function, which lets

you pass the number implied of decimal places?

'----- start of code -----

Function fncZonedToNumber( _

ZonedValue As Variant, _

Optional DecimalPlaces As Integer = 0) _

As Variant

Dim strValue As String

Dim strLast As String

If IsNull(ZonedValue) Then

fncZonedToNumber = Null

ElseIf VarType(ZonedValue) <> vbString Then

fncZonedToNumber = CVErr(5) ' invalid argument

ElseIf Len(ZonedValue) = 0 Then

fncZonedToNumber = Null

Else

strLast = Right(ZonedValue, 1)

strValue = Left(ZonedValue, Len(ZonedValue) - 1)

If InStr(1, "0123456789", strLast, vbBinaryCompare) Then

strValue = strValue & strLast

ElseIf InStr(1, "ABCDEFGHI", strLast, vbBinaryCompare) Then

strValue = strValue & Chr(Asc(strLast) - 16)

ElseIf InStr(1, "JKLMNOPQR", strLast, vbBinaryCompare) Then

strValue = "-" & strValue & Chr(Asc(strLast) - 25)

ElseIf StrComp(strLast, "{", vbBinaryCompare) = 0 Then

strValue = strValue & "0"

ElseIf StrComp(strLast, "}", vbBinaryCompare) = 0 Then

strValue = "-" & strValue & "0"

Else

fncZonedToNumber = CVErr(5) ' invalid argument

Exit Function

End If

If DecimalPlaces = 0 Then

fncZonedToNumber = Val(strValue)

Else

fncZonedToNumber = Val(strValue) / (10 ^ DecimalPlaces)

End If

End If

End Function

'----- end of code -----

Testing in the Immediate window:

?fncZonedToNumber("00000015293F", 2)

1529.36