With your 11 position numeric in B1, the formula to convert it should be:


=IF(ISNUMBER(-RIGHT(B1,1)),B1/100,
(LEFT(B1,10)&MOD(CODE(RIGHT(B1,1))-73,52))/-100)




On Wed, 16 Nov 2005 05:44:46 -0800, "c62ip64"
wrote:

ok, here is an example of the input. There are 3 numerics, each numeric is 11
positions with 2 decimal places.

'000000152350000243577Q00000000000'

The row output, 3 cells, is the following:

152.35 -24,357.78 0.00

Thanks,
Tom




"Ron Rosenfeld" wrote:

You could probably process it using the SUBSTITUTE worksheet function. If you
post some examples of inputs and associated outputs, I can help work it out.



On Tue, 15 Nov 2005 12:38:01 -0800, "c62ip64"
wrote:

The sign is contained in the low order bits of the last character.
x'D0' - } translates to -0
x'D1' - J translates to -1
x'D2' - K translates to -2
x'D3' - L translates to -3
etc.

Sounds like Excel does not recognize this format and I'll need a script to
interpret the results.

Thanks,
Tom

"Ron Rosenfeld" wrote:

On Tue, 15 Nov 2005 06:06:01 -0800, "c62ip64"
wrote:

Thanks, I'm getting an 'invalid name error' when using the formula but the
second option formats the value with the decimal point placement.

You would get a #NAME error if the cell reference that you substituted for
cell_ref in the formula was not valid in your worksheet.


I have one additional question about formatting negative values. How do I
format a negative value from the test file, '0005400}', as '-000540.00'.

How do you know that the value is negative?


--ron


--ron


--ron