Crystal Reports extracting text within delimiters

Question

The software we use is limited to only allowing one manufactures code per product, and this causes problems when we have multiple suppliers for one product.
What I'm trying to do is have all the codes in one field and separate them with Crystal Reports on purchase orders.

The field looks like this:

WESTC(67102)WESTC,NAVIG(EFC-15)NAVIG

one code I've tried was this:

stringvar array x := split({Product.ManufacturerCode},{Supplier.SupplierCode});

x[2]

(I thought it would pick up whatever is before the last "WESTC")

But it does not work, I have also tried using MID with INSTR.

I can reformat the field if there is a better way of doing it.

Answer 1

I think something like this will work:

MID(P, 
    INSTR(1, P, S) + LEN(S) + 1, 
    INSTR(INSTR(1, P, S) + 1, P, S) - INSTR(1, P, S) - LEN(S) - 2)

Replace P to {Product.ManufacturerCode} and S to {Supplier.SupplierCode}.