So a typical example might look something like this?
5431.51 5431.51-5431.5115431.51 /codeWhere the first number is 5431.51, the second should be -5431.51, the third should be 5431.51, and the last is 15431.51?
If[/B] this never floats along the line (your decimals are always in the same positions), and /Bthe positions immediately to the left and right of the value can contain only a space, a minus sign, or a number 1 to 9, then here's my suggested solution.
Let's refer to those variable space/minus sign/number as unknown fields X, where there's a single character X field between each number, simply named X1 through X5, in these positions:
-1*if(x1 .notin. (" ","-"),
if(x1 .notin. (" ","-"),
If the X value to the left of the value (X1) isn't "-" or " ", then add that relative value to the original value, and if the x value to the right of the the original value (X2) is "-" then negate the original value.
The slightly complicated formula should account for the (unlikely?) situation wherein you'd have a number in the left X position and a "-" in the right X position.
Duplicate the calculated field, changing the field references as necessary, and if my assumptions as described above are correct, then that ought to do it for you.
Clear as mud?
I probably should have mentioned in my original post that I am not particularly technical. But let me play around with this and see what happens.
Is there some way that I can upload a sample of the report to this forum (txt file or screenshot) for potential respondents to view?
Thanks again for your help,
I came across a similar issue and resolved it by first trapping the two fields and naming them with a PRE and then used an if statement to correct the data. In my sample below, there is either a 1 or a - occuring in the same vertical column, the 1 in the millions position would be trapped in the wrong field. But I extracted these two fields as characters and then used the following calc fields to correct the data.