I want to input a data ex) apple=2, banana=3

and when I type

2 apple + 3 banana

I want

13.

message edited by onionoh

✔ Best Answer

Here are 2 options for a "multiple cell" entry of your items. I am going to use 5 items as the max, you could use more or less.

Option 1Start with this table in C1:D5

A B C D E 1 apple 2 (Formula Here) 2 banana 3 3 orange 5 4 pear 1 5 lime 4

Place this formula in E1:=A1*IFERROR(VLOOKUP(B1,$C$1:$D$5,2,0),0) +

A2*IFERROR(VLOOKUP(B2,$C$1:$D$5,2,0),0) +

A3*IFERROR(VLOOKUP(B3,$C$1:$D$5,2,0),0) +

A4*IFERROR(VLOOKUP(B4,$C$1:$D$5,2,0),0) +

A5*IFERROR(VLOOKUP(B5,$C$1:$D$5,2,0),0)Enter your values and items in A1:B5. e.g.

A B C D E 1 4 pear apple 2 (Formula Here) 2 1 orange banana 3 3 2 apple orange 5 4 pear 1 5 lime 4The formula will do a VLOOKUP of each item and then do the math to get the total value. You will notice that the entries I made in A1:B3 are not in any particular order, they don't have to be.

Option 2This options eliminates the need to enter the items in Column B. All you need to do is enter the values in Column A. The values must line up with the specific item.

Start with this:

A B C D 1 apple 2 (Formula Here) 2 banana 3 3 orange 5 4 pear 1 5 lime 4Enter this in D1:

=A1*VLOOKUP(B1,$B$1:$C$5,2,0)+

A2*VLOOKUP(B2,$B$1:$C$5,2,0)+

A3*VLOOKUP(B3,$B$1:$C$5,2,0)+

A4*VLOOKUP(B4,$B$1:$C$5,2,0)+

A5*VLOOKUP(B5,$B$1:$C$5,2,0)Now, just put your values next to the items you need:

A B C D 1 2 apple 2 (Formula Here) 2 banana 3 3 1 orange 5 4 4 pear 1 5 lime 4It's interesting that the "easiest" of all 3 options (these 2 and the 1 from the previous post) is Option 2 here. It is not only easier for the user since all you have to do is enter the values next to the items, but it also happens to use the least complicated formula.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

This is not a "Simple Math Question" as far as Excel is concerned. If you plan to enter this string in a single cell then you are asking for a fairly

complicated formula or macro.The string would have to be parsed to extract the "words", then look up the words in a table to find the associated value, then determine what kind of operation you want to perform (based on the operator entered) then finally perform the operation.

Even if every string was of the same "format" as your example:(number, word, plus sign, number, word) the formula would be fairly complicated. If you plan on adding a third word and/or other operators, you are looking at something pretty complex.

If these values will be in separate cells (or can be parsed into separate cells) things may get a little easier, but if there is no consistency in the format/length of the string, things get very complicated again.

Maybe if you explained

whyyou want to do this, we could offer a method/process that might work for you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

um.. its for a game.....

like mine craft.ex)

if I want to make a window

I will need glass + woodand to make glass I will need

rock + fire(something like that I don't really know minecraft)

and I wanted to make a chart with every single items with total consumed time and total value and total money i will make in return.

lol so if this is wayyyyy out of my league I can simply give up...

You didn't address the issues I mentioned in my response, so I'll ask the questions directly: A - Will you be entering this string in a single cell e.g

A 1 2 apple + 3 bananaOr will you be entering this string across multiple cells e.g

A B C D E 1 2 apple + 3 bananaB - Will the operation always be "addition', i.e. a plus sign?

C - Will it always be just 2 items or might you have more? e.g.

2 apple + 3 banana + 1 orange

If it will be more, is there a maximum number of items to deal with?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

A- single cell.

B- comma..? ( , ) but I could use + if that helps

C- might be more.

maximum... not sure.

probably something less then 5 kinds of items

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

mustbe 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 4Enter 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.

Here are 2 options for a "multiple cell" entry of your items. I am going to use 5 items as the max, you could use more or less.

Option 1Start with this table in C1:D5

A B C D E 1 apple 2 (Formula Here) 2 banana 3 3 orange 5 4 pear 1 5 lime 4

Place this formula in E1:=A1*IFERROR(VLOOKUP(B1,$C$1:$D$5,2,0),0) +

A2*IFERROR(VLOOKUP(B2,$C$1:$D$5,2,0),0) +

A3*IFERROR(VLOOKUP(B3,$C$1:$D$5,2,0),0) +

A4*IFERROR(VLOOKUP(B4,$C$1:$D$5,2,0),0) +

A5*IFERROR(VLOOKUP(B5,$C$1:$D$5,2,0),0)Enter your values and items in A1:B5. e.g.

A B C D E 1 4 pear apple 2 (Formula Here) 2 1 orange banana 3 3 2 apple orange 5 4 pear 1 5 lime 4The formula will do a VLOOKUP of each item and then do the math to get the total value. You will notice that the entries I made in A1:B3 are not in any particular order, they don't have to be.

Option 2This options eliminates the need to enter the items in Column B. All you need to do is enter the values in Column A. The values must line up with the specific item.

Start with this:

A B C D 1 apple 2 (Formula Here) 2 banana 3 3 orange 5 4 pear 1 5 lime 4Enter this in D1:

=A1*VLOOKUP(B1,$B$1:$C$5,2,0)+

A2*VLOOKUP(B2,$B$1:$C$5,2,0)+

A3*VLOOKUP(B3,$B$1:$C$5,2,0)+

A4*VLOOKUP(B4,$B$1:$C$5,2,0)+

A5*VLOOKUP(B5,$B$1:$C$5,2,0)Now, just put your values next to the items you need:

A B C D 1 2 apple 2 (Formula Here) 2 banana 3 3 1 orange 5 4 4 pear 1 5 lime 4It's interesting that the "easiest" of all 3 options (these 2 and the 1 from the previous post) is Option 2 here. It is not only easier for the user since all you have to do is enter the values next to the items, but it also happens to use the least complicated formula.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thank you so much !!!

XD

now I can play my game more efficiently!

Ask Your Question

Weekly Poll

Do you think Salesforce should have bought Slack?

Discuss in The Lounge

Poll History