Substract String 5

[codrutza]

CR XI

I have to write a formula which substracts from fields like this:

“Far 32 Pallets Temp - 10 to +15 Degrees C Maa Montra 500 MG 50 x 10 Pack”
“Bie 57 Pallets Temp - 2 to +10 Degrees Celsius Ca 200 MG 30 x 100 Packets”
....

Desired Output:

“32 Pallets”
“57 Pallets”
...

Could you help, please?

 

[briangriffin]

This assumes that the string you want always begins with the first numeric character, and always ends before the first occurrence of the word 'Temp'. Both these assumptions are probably not true for all your records, but this should get you started:

whileprintingrecords;

numbervar x;
numbervar y := 1;
numbervar z;

while not(isnumeric({YourDBField}[y]))
do 
(x := y;
y := y + 1);

z := instr({YourDBField},"Temp") - 1;

mid({YourDBField},x,z - x)

 

[codrutza]

Thanks for your reply, Brian. I'm sorry, I can't get it. Could you explain, please?

 

[codrutza]

Sorry, the field I have is memo. And sometime I have "42 Pallets Ret - 10 to +25Degrees C Fe 500 MG 50 x 10 Pack", indeed.

 

[briangriffin]

No time to test this, but try:

1. Create a formula called {@ExtractString} - replace YourMemoFieldName with the actual memo field:

left(replace({[b]YourMemoFieldName[/b]},100)

2. Modify the original formula to this:

whileprintingrecords;

numbervar x;
numbervar y := 1;
numbervar z;

while not(isnumeric({@ExtractString}[y]))
do 
(x := y;
y := y + 1);

z := instr({@ExtractString},"Temp") - 1;

mid({@ExtractString},x,z - x)

If this doesn't work for you please post the error message, etc. Once this is working then we can address the records that have "Ret" in them.

 

[kenhamady]

So far it seems that the word "Pallet" is always right after the number. Is that true? Is there always a space before the number?

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[Charliy]

I played with it and came up with this:

if isnumeric(split({TTEXT.TFFL},' ')[1])
then split({TTEXT.TFFL},' ')[1]&' '&split({TTEXT.TFFL},' ')[2]
else
if isnumeric(split({TTEXT.TFFL},' ')[2])
then split({TTEXT.TFFL},' ')[2]&' '&split({TTEXT.TFFL},' ')[3]

 

[codrutza]

Thank you all for your replies.
Briangriffin
For the formula @ExtractString I get the error "Not enough arguments have been given to this function"
Ken Hamady:
If I understood right your question: Sometime "53Pallets", sometime "53 Pallets", sometime "Bie 57 Pallets"...I assume that the users don't input "Bie57 Pallets"
Charliy
Error: A subscript must be between 1 and the size of the array

 

[kenhamady]

And what is the largest number of pallets you will see?

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[BettyJ]

[tt] left({Sheet1_.String},(instr({Sheet1_.String},"Pallet"))-1);[/tt]

 

[GJParker]

This assumes that the string always refers to pallets

strreverse(ToText(val(strreverse(split({YourField},'Pallet')[1])),0,'')) & ' Pallets'

Gary Parker
MIS Data Analyst
Manchester, England

 

[kenhamady]

I like Gary's method, but I was going to avoid it if the range of possible values was small. But since he went to the trouble of starting it I will finish it for him. It needs one small tweak to deal with numbers that end in zero. For instance the numbers 10 and 100 (when you val() them in reverse) will become just 1. I know because I ran into this before:

http://kenhamady.com/cru/archives/2206

The following version fixes the problem by adding a 1 to the end of the number just before the VAL() happens, then stripping it back off at the end.
strreverse(ToText(val(strreverse(split({YourField},'Pallet')[1] &'1')),0,'')) [ 1 to -2 ] & ' Pallets'



Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[BettyJ]

Sorry, what I posted earlier was only half of the solution. The below solution will work if there is a space before the number of pallets and there is only one word (if there is)
before the number of pallets.

if isnumeric(left({YourField},(instr({YourField},"Pallet"))-1)) then
left({YourField},(instr({YourField},"Pallet"))-1) &" Pallets"
else
split(left({YourField},(instr({YourField},"Pallet"))-1)," ")[2] & " Pallets

 

[codrutza]

Thank you all. The records are far too complicated to put a criteria, (eg there are other names not only pallets); we asked the programmers to put 3 fields instead (32 / Pallets / Description). I will use your replies for other situations, thank you very much.