extract number from string

[grom71]

Hi all,
I'm trying to extract the strength of a drug from a string.

The string {xdailyissues.drug_strength} can be any one of the following formats:
125 mg
2 g
1.2 mega unit
200 mg in 5 ml
100mg in 5ml

I want the formula to output the result in milligrams. For those of you who dont know 1000mg = 1 gram.
I only need the number.

Therefore in the examples above:
125 mg would output= 125
2 g would output= 2000
200 mg in 5ml would output= 200
1.2 mega unit would output= 1200 (a mega unit is a gram !)
100mg in 5ml would output= 100

The numbers could be different they are all not what is described above for example it might be:
375mg in 5ml or
2.2g

Hope all that makes sense....
I'm really stuck with this one...
Any help would be gratfefully recieved... !
Steve



Authorised re-seller of CRD (a powerful scheduling tool for automating Crystal reports)
Visit

http://www.reports-for-healthcare.com

for 10% discount.

 

[dgillz]

Sounds like you need 2 fields, qty and unit of measure. Sounds like you need a new dba for allowing this to happen.

Val({yourfield}) will return the number as long as it is to the far left of this string. I have no idea how to grab the unit of measure. I wound need a set of rules to go by.

You could create a formula field called multiplier, but you would need to be very careful of the order of the following tests:

If instr(uppercase({yourField}),"MEGA")>0 then 1000 else
If instr(uppercase({yourField}),"MG")>0 then 1 else
If instr(uppercase({yourField}),"G")>0 then 1000

Notice if you put the last line first, every single record would evaluate to 1000.

Then take this result and multiply it by the val() formula.

Fire that dba first!

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[grom71]

Hey dgillz,
I managed to do it..
Thanks for your help
Steve

Authorised re-seller of CRD (a powerful scheduling tool for automating Crystal reports)
Visit

http://www.reports-for-healthcare.com

for 10% discount.

 

[dgillz]

OK, did you fire your dba? If so then the job is done.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry