|OK, just to show you what we are up against, I came up with a formula that will work for your "single cell, 2 item" example. I then came up with an alternative that will work for more than 2 items, but it can't be used with a "single cell" entry. I am going to post the alternative suggestion in a separate response just for clarity.|
For the "single cell, 2 item" entry, the following formula seems to work, but these rules must be followed:
1 - There can only be 2 items. While it's possible to create a formula that will work with more than 2 items in a single cell, I'm not even going to try - you'll see why later. I'll leave that up to you to try if you want.
2 - There must be 2 items separated by a + sign, e.g. "2 apple + 3 banana" will work. "2 apple" will not.
3 - The "items" can only be a single word, e.g. "apple " will work. "red apple" will not.
4 - There can not be any trailing spaces, leading spaces or extra spaces within the string. Just the single spaces as used in your example.
OK, with all that said, here is what I came up with.
Start with your string in A1 and the following table in C1:D5
A B C D
1 2 apple + 3 banana (Formula Here) apple 2
2 banana 3
3 orange 5
4 pear 1
5 lime 4
Enter this formula in B1 and you should get 13:
=MID(A1,1,FIND(" ",A1)-1) * VLOOKUP(MID(A1,FIND(" ",A1,1)+1,
FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1,1)-1),$C$1:$D$5,2,0) +
MID(A1,FIND("+",A1)+1,FIND("^",SUBSTITUTE(A1," ","^",4))-FIND("+",A1)) * VLOOKUP(MID(A1,FIND("~",SUBSTITUTE(A1," ","~",4))+1,LEN(A1)),$C$1:$D$5,2,0)
As you can see, it takes a pretty complex formula to deal with just 2 items. In order for it to work more - especially with a varying number of items - it would become ridiculously cumbersome, although not impossible.
In my next response, I'll offer an alternative than can be used if you are willing to use multiple cells for your "items".
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.