Extracting numbers from a string in crystal reports 9

Question

I am trying to perform a quick fix for a client. We have a report field that shows tolerance values in strings like +/-20 , -19 +18. This is in micrometer and the client wants it in millimeter. So i need to divide only the numeric part of this string by 1000 and display the result.

I am relatively new to crystal reports, with my limited knowledge and from searching this site for suggestions, i created a function with the following code lines,

Function (stringvar x)

local stringvar array input := split(x,"+/-");

val(input[ubound(input)])/1000

The above function works perfectly for tolerance values of +/-. However i am not able to figure out a way to do it for '-19 +18'. I would like to have the result as -0.019 +0.018

I can easily do it in the database source and send it to the report. However the client needs a quick fix of just the report. Any help would be greatly appreciated.

Answer 1

I figured out an answer which would work for this specific case. I used the following conditions in a formula field. Based on the condition I called a User defined function 'Tolerance','Tolerance2', 'Tolerance3'.

Formula field:

IF (Left ({PDPRINTDATA.PD_T45}, 3 )="+/-") THEN
'+/-' + ToText(Tolerance({PDPRINTDATA.PD_T45}),3)
ELSE IF (Left ({PDPRINTDATA.PD_T45} , 1 )="-") THEN
'-' + ToText(Tolerance2({PDPRINTDATA.PD_T45}),3) + '  +' + ToText(Tolerance3({PDPRINTDATA.PD_T45}),3)

Tolerance:

Function (stringvar x)
local stringvar array input := split(x,"+/-");
val(input[ubound(input)])/1000;

Tolerance2:

Function (stringvar x) local stringvar mystr := x; 
If NumericText (mystr[2 to 3]) Then
 ToNumber(mystr[2 to 3])/1000
Else 0

Tolerance3:

Function (stringvar x)
local stringvar mystr := x;
If NumericText (mystr[7 to 8]) Then
ToNumber(mystr[7 to 8])/1000
Else
0

This solution works for me considering the fact that the string will always be of this format '+/-XX' or '-XX +YY'.

Answer 2

try this

if(Left (x, 3 )="+/-")
then ToNumber(split(x ,"+/-")[2])/100
else if(Left ({x , 1 )="+")
then ToNumber(split(x ,"+")[2])/100
else if(Left (x , 1 )="-")
then ToNumber(split(x ,"-")[2])/100