Find biggest number in a string

So basically, in a datasheet of orders I have a column that lists properties of each order, a cell that usually contains this value: "/", meaning no specific properties.

However, if the order consists of let's say 8 elements, in that column I'll have cells with the entries "L1/8", "L2/8", "L3/8", ... etcetera until the entry "L8/8", meaning item 8 out of 8 items. Sometimes, that column will also list other properties, such as "cardboard" or "plastic" for the packaging or "trad" for traditional shipment, but as far as I know it won't list anything with numbers in it. So, although usually the cells in that column are just "/", they might be "L3/12, trad, plastic". In no specific order.

Now, I want to have a formula that returns to me, how much elements does the order with most elements count?

So basically, out of the following strings, it should return 7 for example.

/
/
L1/5
L2/5
L3/5
L4/5
L5/5
plastic, L1/3
L2/3
L3/3, trad
cardboard
/
L2/7
L3/7
L5/7, trad
L6/7

Does anyone have any ideas?

Cheers,

HosteDenis

Originally Posted by nflsales

will you please attach a sample file with expected result

I have, in this post now.

problem.xlsx

Originally Posted by nflsales

If you have your data in Column a try below array formula and change the range if needed

=MAX(IFERROR(MID(A1:A22,SEARCH("/*,",A1:A22&",")+1,SEARCH(",",A1:A22&",",SEARCH("/*,",A1:A22&","))-SEARCH("/*,",A1:A22&",")-1)+0,0))

since the above is an array formula pls confirm the same with Shift+Ctrl+Enter

Tried it, it returns zero (if I did everything right, so I don't think it works unless I entered it wrongly).

Did you enter with Shift+Ctrl+Enter? It worked for me using the data you supplied.

It works, thanks a lot.

Yeah sorry, apparently I didn't treat the formula as an array formula (although I've worked with array formula's before!).

My bad, it works now! Thanks