Multiply Cell Values which include text units
 

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.

Answer: Multiply Cell Values which include text units
 

Yes, you can multiply cell B13 which has a text unit with cell C13 which has a currency format by using a combination of functions in Excel. Here's how you can do it:

1. First, you need to extract the numeric value from cell B13. You can do this by using the following formula in cell D13:
   
   
   Code:

   ---

   =VALUE(LEFT(B13,FIND(" ",B13)-1))

   ---

   This formula will extract the numeric value from cell B13 and ignore the text "Units".
   
2. Next, you need to multiply the value in cell D13 with the value in cell C13. You can do this by using the following formula in cell G13:
   
   
   Code:

   ---

   =D13*C13

   ---

   This formula will multiply the numeric value from cell B13 with the currency value in cell C13 and give you the result in cell G13.

By using these formulas, you can multiply cell values which include text units and currency formats without receiving the "#value!" error in the formula cell.

Multiply Cell Values which include text units
 

Try

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

--

HTH

Bob

"DaveR" wrote in message
...
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.

Multiply Cell Values which include text units
 

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" wrote:

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.

Multiply Cell Values which include text units
 

Thank you for the direction.

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

"pmartglass" wrote:

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" wrote:

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.

Multiply Cell Values which include text units
 

I like that

"Bob Phillips" wrote:

Try

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

--

HTH

Bob

"DaveR" wrote in message
...
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.


.

Multiply Cell Values which include text units
 

DaveR wrote:
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)

Multiply Cell Values which include text units
 

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

--

HTH

Bob

"pmartglass" wrote in message
...
I like that

"Bob Phillips" wrote:

Try

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

--

HTH

Bob

"DaveR" wrote in message
...
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.


.