Extract Product Weight from description

[strom99]

I have the following field

55 gal container (750 lb, 341 kg)
Holder (11 lb, 5kg)

I need to be able to extract the 750 lb, 341 kg in order to sort on this field.

Crystal 8.5

Thanks!

 

[rosemaryl]

Dear Strom99,

If this is always the format you show above, then this formula should work:

Mid({Table.WeightField},instr({Table.WeightField},"(&quot,length({Table.WeightField}))


This will return (750 lb, 341 kg)

If you need to remove both parens adjust to this:

Mid({Table.WeightField},instr({Table.WeightField},"(&quot+1,length(Mid({Table.WeightField},instr({Table.WeightField},"(&quot+2))))

Hope that helps,

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[synapsevampire]

Since these are the same, with two different measurement systems, and since you want to sort (meaning you want numeric values):

if isnumeric(mid("Stuff (750 lbs )",1+instr("Stuff (750 lbs )","(&quot,1)) then
val(mid("Stuff (750 lbs )",1+instr("Stuff (750 lbs )","(&quot,
instr("Stuff (750 lbs )","l&quot-(1+instr("Stuff (750 lbs )","(&quot)))
else
0

Replacing
"Stuff (750 lbs )"
with your field name.

This also verifies that it's a number after the first ( character and returns 0 (so they would sort first).

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[synapsevampire]

Perhaps I should clarify why you need a numeric:

Given the values:

(1 lb...)
(2 lb...)
(10 lb...)
(100 lb...)
(200 lb...)
(9 lb...)

If it's a string, then you'd get the following sort:

(1 lb...)
(10 lb...)
(100 lb...)
(2 lb...)
(200 lb...)
(9 lb...)

If it's a value, you'd get:

(1 lb...)
(2 lb...)
(9 lb...)
(10 lb...)
(100 lb...)
(200 lb...)

Remember, you don't have to show the field you sort on, it simply allows for proper sortation.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[rosemaryl]

Dear SV,

I hadn't thought that through for him as regards the sorting....

Your solution is much better!

Thanks,

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.