I don't get the same result as you. That mnay not help much ...
Firstly if I set up your value samples as lines in a notepad file formatted as
and then read the file using Monarch, trap on the $ , define the field and view the table I get the numbers without any formatting just as you want them EXCEPT for the 00 placeholder. It's a numeric field so multiply by 100 and you will have the result you want. Then convert to character if you need to.
If I make the incoming field character (as you may need to have as part of your extraction of course) and apply you formula it works fine BUT the disply is not consistent if it was originally right justified.
$ 760[/font][/quote]becomes something like
76000[/font][/quote]Which makes me wonder if you are actually getting the correct result but have a display setting anomaly which is hiding the second concatenated zero from the 76000 value. Is the calculated field set to left or right justified display? Is the field wide enough for both data and display?
I could be missing things by a long way but this looks like it should be simple as you say so for me it's worth checking the simple things with you first.
[size="1"][ July 19, 2007, 05:24 PM: Message edited by: Grant Perkins ][/size]
Initial review considering your feedback:
Original and Calculated fields are:
Alignment = Right
*I applied 'Autosize Clmn Width', no help.
I tried changing alignment but same result.
I have also noticed when I type $760 as a TEST within the calulated field formula. . I get the expected result.
But, seemingly does not carry through.
Weee, my first attempt using the CODE function here to show what I'm seeing.
$1,301,027 130102700 $1,301,027 130102700
$361,550 36155000 $361,550 36155000
$50,963 5096300 $50,963 5096300
$760 7600 $760 7600
$340 3400 $340 3400
$165,939 16593900 $165,939 16593900
An example of the report display:
$165,939 $165,939 /font[/quote]
In your calculation you are looking for a "$," which is not present for 760. (it only has $)
Strip(,"$,")+"00" /quoteGrant's idea is the best solution, pick up the numbers as numbers, multiply by 100 and export (or change to text first). That way you do not need to worry about $ or comma(s) in the number.
If I type you report display (looks right justified) into a txt file and then display that in Monarch I get
$165,939 /font[/quote]Which is interesting.
I still think it is something to do with the display. What happens if you set the DISPLAY AND DATA width for the field to, say, 20 ?
Also try making the Alignement LEFT rather than right.
Can you see everything then?
We can deal with the display format once we have found the missing characters!