Extract part of a field using MID

[GCL2007]

Hi,
I'm trying to extract a portion of a field. The fields value might be "0123456-PO-555556666 SOMETEXT OR NUMBERS" .
I just want to extract the PO number portion, which would be 555556666. I've been playing with a MID({TABLE.FIELD},InStr({TABLE.FIELD},'PO-')+3) formula, which seems to work to exclude the 0123456-PO- portion which is great. I'm still struggling on how to remove the stuff after the 555556666 (need to eliminate the space and the SOMETEXT OR NUMBERS. Any ideas on how to do that in one formula?

 

[kray4660]

seems like you encase the above formula in a left function (i.e., LEFT( MID({TABLE.FIELD},InStr({TABLE.FIELD},'PO-')+3), INSTR(MID({TABLE.FIELD},InStr({TABLE.FIELD},'PO-')+3), ' ') -1) ). This has not been tested.

 

[briangriffin]

Just for another approach, you could use a loop if the first character after the PO number is always non numeric (and the actual PO number is always numeric):

whileprintingrecords;
stringvar v_text := MID({YourField},InStr({YourField},'PO-')+3) ;
stringvar v_PO;
numbervar x := 1;
while isnumeric(v_text[x])
do
(v_PO := v_PO + v_text[x];
x := x + 1);

v_PO

 

[pmax9999]

Or as long as the PO number is always numerical, you could use:

VAL(MID({TABLE.FIELD},InStr({TABLE.FIELD},'PO-')+3))

I'm guessing the length of the PO number isn't consistent, but if it is always 9 characters long you could use:

MID({TABLE.FIELD},InStr({TABLE.FIELD},'PO-')+3, 9)

Cheers
Pete

 

[Charliy]

If it always looks like nnn-PO-nnnn then you can use split.

Split({table.field},"-",3) will turn the field into an array of three elements, and return the third one.