get only number from string and find sum in crystal report formula

smit bhavsar
smit bhavsar used Ask the Experts™
on
hello everyone
i want one help from you that  

example   : i have one database field  it contain some value like  

1)   abc-100,abc2-110,defa-200,xyzx-10  
2)   abcd-100,abc1-110
3)   xyza-10,zzz1-150
4)   pqrs-50,
5)   pppzab-150,pabc-150


i want answer like
1)420
2)210
3)160
4)50
5)300

i hope u all are understand and help me to solve that problem in crystal report
please help me friends  i am new for crystal report so please  
thanks


thanks
and regards smit
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
James0628

Commented:
Based on what you posted, I don't think this will be too difficult, but I don't have time to get into it right now.  I also wanted to check some things.

 Will the values in the field (eg. "abc-100" and "defa-200") always be separated by commas?

 Is the number that you're interested in always at the end of each substring, and always after a "-"?

 If the answer to those questions is "yes", I don't think it will be a problem.  I'll post something later, if someone else doesn't beat me to it.  :-)

 James
smit bhavsar

Author

Commented:
hello jeams
yes you r right value separate by comma and there's  underscore between number and string
if u have any idea about this question answer then plz reply me the answer because i have no idea about crystal report  so please help me

thanks
smit
Commented:
This formula works for your example values.

Local StringVar Array values;
Local NumberVar total;
Local NumberVar i;

values := Split ({your_field}, ",");

for i := 1 to UBound (values) do
  if InStr (values [ i ], "-") > 0 then
    total := total + Val (Split (values [ i ], "-") [ 2 ]);

total

Open in new window


 Use Split to separate the comma-separated values and store them in the values array.
 Loop through the values in the array, and if a value contains a "-", use the "-" to split that value into another array, and use Val to convert the part after the first "-" ( [ 2 ] ) into a number, and add that to total.

 If the field could ever be null, the formula may need to be changed to handle that.

 James

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial