LEFT RIGHT MID complex problem

Hi,

I am having some problems pulling out what I need from the data below. The number(s) before the apostrophe is the pack size, e.g. 10'S, 30'S etc. and I need to extract this number to a new column so then I can do a multiplication on it.

I can't use text to columns as I have around 40k rows of data and the number of letters and spaces before the pack size changes all the time, so I would have to manually go and realign all the data.

I've tried a lot of things using left, right and mid but can't get it to work. It seems excel can't count backwards if I use a mid and find the apostrophe then try to return -2 numbers before it.

Thanks in advance!

Product Pack size
ABCD OAD CAPS 120 MG 10'S FGHI 10
ABCD OAD CAPS 120 MG 30'S 30
ABCD OAD CAPS 120 MG 30'S FGHI 30
ABCD OAD CAPS 200 MG 10'S etc.
ABCD OAD CAPS 200 MG 10'S FGHI

Thanks Olly. Unfortunately the number is sometimes 1 digit, sometimes, 2, sometimes 3.

Any ideas?

Ha! got it!

=IF(VALUE(MID(E500,FIND("'",E500)-2,2))>0,VALUE(MID(E500,FIND("'",E500)-2,2)),VALUE(MID(E500,FIND("'",E500)-3,3)))

Thanks for the inspiration