Prev: what function to use?
Next: create a button to print certain tabs in excel 2003
From: DaveR on 11 Mar 2010 10:50
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.
From: Bob Phillips on 11 Mar 2010 10:54
Try

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

--

HTH

Bob

"DaveR" <DaveR(a)discussions.microsoft.com> wrote in message
news:845C88D1-F217-4E0F-889E-84AAAD7FA35C(a)microsoft.com...
>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.


From: pmartglass on 11 Mar 2010 11:13
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.
From: DaveR on 11 Mar 2010 11:28
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.
From: pmartglass on 11 Mar 2010 11:43
I like that

"Bob Phillips" wrote:

> Try
>
> =SUBSTITUTE(B13," Units","")*SUBSTITUTE(C13," / Ea","")
>
> --
>
> HTH
>
> Bob
>
> "DaveR" <DaveR(a)discussions.microsoft.com> wrote in message
> news:845C88D1-F217-4E0F-889E-84AAAD7FA35C(a)microsoft.com...
> >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.
>
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: what function to use?
Next: create a button to print certain tabs in excel 2003