Multiplication of cells containing words

Question

I'm making a finance plan, where I have the columns 'Quantity', 'Cost' and 'Total'.

The quantity column will contain how many of the item I buy.
The Cost column will have the individual cost of the item.
The total column will have, using excel formulas, the PRODUCT of the quantity cell and cost cell. For example, if I buy 2 of one item that costs $2 each, the total cell will contain the formula "=PRODUCT(QTY:COST)" to produce $4.

My dilemma is, that I want to be able to have both words and numbers in the quantity cell, but still have the formula in the total cell to work. For example, if I buy two packets of something, I want to write in the quantity cell '2 pkt' instead of just '2', but when I do this the total cell does not pick up the number in the quantity cell and just gives me 1 x Cost when it should give me 2 x Cost.

Is there any way to fix this while still using formulas, so I don't have to enter all totals manually? Any help appreciated!

Solution

With the assumption that there is at least one space between the number and text in the same cell, try the following.

The formula can be

=IF(ISNUMBER(C5),C5*B5,VALUE(LEFT(TRIM(C5),FIND(" ",TRIM(C5),1)-1))*B5)

and to prevent error where it's just a number entered as text, use

=IF(ISNUMBER(VALUE(C5)),C5*B5,VALUE(LEFT(TRIM(C5),FIND(" ",TRIM(C5),1)-1))*B5)

Comments

[patkim]

Is it ensured that the Number in Qualtity shall be followed by text with a space in between, always. e.g. '3 Pkts', '20.6 grams', '6 pieces' etc. Then you can extract the first part of the cell before Space as numeric and use in your formula.

[Baibro]

Yes it is, could you maybe answer telling me how exactly to do that?

[fixer1234]

It's generally bad practice to build a spreadsheet this way, if for no other reason than needing to jump through hoops to use your data. The better way would be to add a column with the text, so the quantity and the unit description would be in adjacent columns.