Multiply Cell Values which include text units

[DaveR]

I have a formula in cell G13 that shows "=B13*C13". Is there a way that i
can multiply cell B13 which has a number format and reads "61 Units" to cell
C13 which has a currency format and contains "34.23 / Ea" to get a value of
$2,088.03 shown in cell G13? I don't know if I can show text in the cell
that is being referenced in a formula.

Can you show text in a cell that is being referenced in a formula without
receiving the "#value!" in the formula cell?

Thanks.

 

[Bob Phillips]

Try

=SUBSTITUTE(B13," Units","")*SUBSTITUTE(C13," / Ea","")

 

[pmartglass]

If you use a custom format to combine text and numbers than you can calculate
on the cells as normal.

I am guessing that you already have your information typed and that it varies.
Depending on how consistant the data is entered it can be done with a
formula combinging left and seek.

I think you would be better served to use the custom format option

 

[DaveR]

Thank you for the direction.

I Custom formatted the cell TYPE: $0.00" / EA" and it worked.

 

[pmartglass]

I like that

Try

=SUBSTITUTE(B13," Units","")*SUBSTITUTE(C13," / Ea","")

--

HTH

Bob




.

 

[Glenn]

I have a formula in cell G13 that shows "=B13*C13". Is there a way that i
can multiply cell B13 which has a number format and reads "61 Units" to cell
C13 which has a currency format and contains "34.23 / Ea" to get a value of
$2,088.03 shown in cell G13? I don't know if I can show text in the cell
that is being referenced in a formula.

Can you show text in a cell that is being referenced in a formula without
receiving the "#value!" in the formula cell?

Thanks.

Another way:

=LEFT(B13,FIND(" ",B13)-1)*LEFT(C13,FIND(" ",C13)-1)

 

[Bob Phillips]

But yours is a better way. I also thought of suggesting that, but just
decided to give him a 'straight' answer