How to make excel formula to ignore text in formula

[balasahebgarad]

To make the formula more interactive, is there any way we can write text and numbers in formula and excel perform mathematical operation ignoring the text

Cell A1= 8 trucks * 6 cubic meter per truck * 100 trips;
and answer should be 480.

Is it possible ? How ?

[Glenn Kennedy]

You won't like this, but I'm being honest.

Forget it.

This will TOTALLY destroy the fiunctionality of Excel. Unless you ware doing this on a one-off basis, or (maybe) if your texts are TOTALLY standardised, this will be a total nightmare. Maybe someone can come up with a smart-looking way of doing this, but it will drive you mad in the end. Seriously, honestly... just don't go there.

If you want something that LOOKS like that, see the file.

[KokiKoki]

Just for fun, try this at B1.

=PRODUCT(IFERROR(--TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),1+(ROW($A$1:$A$30)-1)*100,100)),1))

[wk9128]

C1 cell , array formula

[kvsrinivasamurthy]

Just for fun only not serious

6*8*100<>480

[wk9128]

6*8*100 should be 4800, not 480 maybe it's a clerical error