Advanced MID Formula (1 Viewer)

[Roly Reefer]

I am trying to get a mid formula that can start exctracting at the first space (which I can do) and stop extracting at the last space, which I can't get to work properly. My current formula is:

MID([PRODUCT DESCRIPTION],INSTR(TRIM([PRODUCT DESCRIPTION])," ")+1,LEN([PRODUCT DESCRIPTION])-INSTRREV(TRIM([PRODUCT DESCRIPTION])," ")-1)

The data I am using to test it with is:

PRODUCT DESCRIPTION
VEGE MINCE SOYA PROTEIN
PASTE MADRAS KNORR
MARMALADE PORTIONS DUERRS
STUFFING SAGE & ONION 3663
PEPPERS SLICED MIXED
APPLE SOLID PACK (KC3)

Thanks very much for any help.

 

[DALeffler]

Try:

MID([PRODUCT DESCRIPTION],INSTR(TRIM([PRODUCT DESCRIPTION])," ")+1,INSTRREV(TRIM([PRODUCT DESCRIPTION])," ")-(INSTR(TRIM([PRODUCT DESCRIPTION])," ")+1))

The number you need is the last space position in the string minus (the first space position + 1).

hth,

 

[Roly Reefer]

Working perfectly - thanks a lot!! Works slightly differently in mid to right.