What functions do you use to pick out numbers from a range 3

[Hugo1]

Does anyone know how to pick the numbers out of a range field like this:
'>26 and <31' o
I've tried using the instring and mid functions but because the numbers in the range vary in size sometimes the formula field brings back the '>' or the part of the 'and' which i don't want. I thought about using nested if statements but i'm not sure where to start.

I'm using Crystal XI.

Any advice would be much apprciated

 

[Madawc]

If the format is always '>NN and <NN', '>N and <NN', '>NNN and <NNN' etc., then you could do conditional MID commands, e.g.

if not NumericText(Mid({field}, 3, 1) 
then Mid({field}, 2, 1)
else if not NumericText(Mid({field}, 2, 1) 
then Mid({field}, 2, 2)

You should be able to develope that to cope with the first number. For the second, do a preliminary split using

Split({field}, "<")[2]

Test the result in the same way.



Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

If you want to compare a number to the values in the string range, then you could use:

//{@1st value}:
val(split({table.string},">")[2])

//{@2nd value}:
val(split({table.string},"<")[2])

-LB

 

[Hugo1]

What if there are decimal places in the format?

 

[synapsevampire]

Use a loop:

whileprintingrecords;
stringvar MyString := "A123 456,67";
stringvar output;
numbervar x;
for x := 1 to len(MyString)do(
if isnumeric(mid(MyString,x,1)) then
Output:=Output+ mid(MyString,x,1);
if not(isnumeric(mid(MyString,x,1)))
and
right(Output,1) <> " " then
Output:=Output+ " ";
);
Output

Or you can use a split on the final output variable as in LB's solution to differentiate them once they are properly extracted.

Note that you say you want to extract them, but don't bother stating how you want to display them, please be thorough.

-k

 

[lbass]

My formulas should work as is even if the numbers contain decimals.

-LB

 

[synapsevampire]

Maybe not, LB,could be formatiing you're not aware of.

whileprintingrecords;
stringvar MyString := "A123 456,67";
stringvar output;
numbervar x;
for x := 1 to len(MyString)do(
if isnumeric(mid(MyString,x,1)) or
mid(MyString,x,1) = "." then
Output:=Output+ mid(MyString,x,1);
if not(isnumeric(mid(MyString,x,1)))
and
right(Output,1) <> " " then
Output:=Output+ " ";
);
Output

-k

 

[lbass]

It works with formatting like:

">235.6 and <34.86"

So far he hasn't indicated variation beyond this...

-LB

 

[Hugo1]

How would i get it to work with >0 and <1

 

[Hugo1]

also >0 and <0.1

 

[Hugo1]

i get the following error:
'A subscript must be between 1 and the size of the array.'

 

[Hugo1]

I'm obviously not using the formulae correctly, this is what i'm doing to try and get the number after the > :

Formula 1: Split({SPECIFICATION_ITEM.DESCRIPTION},">")[2]
Formula 2: Split({@test3}," ")[2]

This works up until i get >0 and < 1 or >0 and 0.1

Hope this makes sense.

 

[synapsevampire]

The output in question is redundant anyway as the second criteria is already covered by the first.

My second formula covers the requirements, however note that you've given no indication what yoyu intend to display, you just state that you want to extract the numbers, please be spoecific.

whileprintingrecords;
stringvar MyString := "A123 456,67";
stringvar output;
numbervar x;
for x := 1 to len(MyString)do(
if isnumeric(mid(MyString,x,1)) or
mid(MyString,x,1) = "." then
Output:=Output+ mid(MyString,x,1);
if not(isnumeric(mid(MyString,x,1)))
and
right(Output,1) <> " " then
Output:=Output+ " ";
);

So now use split functions on the above formula ifyouneed seperate values:

whileprintingrecords;
stringvar output;
stringvar array AllOuput:split(output," ");
if ubound(AllOutput) >0 then
AllOutput[1]
else
"N/A"

whileprintingrecords;
stringvar output;
stringvar array AllOuput:split(output," ");
if ubound(AllOutput) >1 then
AllOutput[2]
else
"N/A"

whileprintingrecords;
stringvar output;
stringvar array AllOuput:split(output," ");
if ubound(AllOutput) >2 then
AllOutput[3]
else
"N/A"

whileprintingrecords;
stringvar output;
stringvar array AllOuput:split(output," ");
if ubound(AllOutput) >3 then
AllOutput[4]
else
"N/A"

So that provides for 4 values.

But every answer satisfied your criteria, as you don't state requirements, rather a vague idea of the data, and less ofwhat the output should be, and everyone extracted the numbers as you asked for, in a fashion...

-k

 

[lbass]

Note that my formulas use the val() function, and do not use a space as a separator. Did you try my original formulas? Why did you change them? What field are you comparing to this range??? Is it a number field or a string? I think it would be best to convert that to a number also, if it is a string, by using the val() function on that and then compare it to my original suggested formulas.

-LB

 

[Hugo1]

Thanks I've got it working now