Backgound on the challenge I am facing:

• A vendor sends us an import file that combines three customer data fields into one, separated by a ‘/’. (These are Unit No / Account No / Order Reference)
• This combined field is imported into our Sage Line 50 as our data field INVOICE_ITEM.JOB_NUMBER
• I need to extract field two and field three from this to report separately to the customer:
  • ‘Order Reference’ ->  This is the third of the three fields and so will always be the last 12 alphanumeric characters of the combined field, in the format FOOXXXXXXXXXX
  • ‘Account Reference’ -> This is the second of the three fields and is usually (but not always) in the format XXXXXX/XXX

Below is an example of what I need extracted from a combined reference 87557001/939688/X52/FOO525020V56

• Unit No = 87557001
• Order Reference = FOO525020V56
• Account Reference = 939688/X52

For order reference, I need a formula that returns the last 12 characters of the field.

For account reference, I need a formula that drops an unspecified number of characters before and including the first ‘/’ and also the last 13 characters including the ‘/’ at the end.

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

This is the syntax I have created, and it is not working:

String=87557001/939688/X32/F00525020V56

              -----------  -----------------  --------------------

              Unit no        A/c ref            order ref

1. Order reference – returns last 12 characters of the string
   1. = right$(string, 12)
2. A/c Ref:
   1. Remove order refer and last ‘/’ from the string and store in string 1
   2. Step1: String 1=left$(string, less(string)-13)
   3. Step 2: find position of first ‘/’ in string1

1. Pos=instr(1, string1, ‘/’)

1. 1. Step 3: Extract everything to the right of the first ‘/’ in string1
   2. A/c Ref = right$(string 1, less(string1)-pos)
2. Unit no: left$(string, instr(1, string, ‘/’)-1
   1. Returns everything before ‘/’

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Thanks for your help on this.

Regards,
David