How to imbed a cell reference in a text field?

[LurfysMa]

Is there a way to imbed the value in a cell in a text field?

Something like: "Cost at [E7] / hour.", where "[E7]" gets replaced by
the value in cell E7.

Thanks

 

[LurfysMa]

Is there a way to imbed the value in a cell in a text field?

Something like: "Cost at [E7] / hour.", where "[E7]" gets replaced by
the value in cell E7.

I found the answer. Use the concatenate function:

=CONCATENATE("Cost at $",E7,"/hour.")

Thanks

 

[Roger Govier]

Hi

Another alternative, with a slight difference in layout would be to
Custom format the cell.
Format>Cells>Number>Custom> #0.00 "$ Cost / hour"

Then in the cell just enter
=E7

The possible advantage is the number value generated from the E7 value
is still a number and can be used in further calculation if required.

--
Regards

Roger Govier

Is there a way to imbed the value in a cell in a text field?

Something like: "Cost at [E7] / hour.", where "[E7]" gets replaced by
the value in cell E7.

I found the answer. Use the concatenate function:

=CONCATENATE("Cost at $",E7,"/hour.")

Thanks

 

[LurfysMa]

An alternative to try as well would be:
="Cost at "&TEXT(E7,"$#,##0.00") &"/hour"

Thanks.

That's the type of solution I was looking for, but couldn't find, when
I stumbled across the Concatenate function.

 

[LurfysMa]

Hi

Another alternative, with a slight difference in layout would be to
Custom format the cell.
Format>Cells>Number>Custom> #0.00 "$ Cost / hour"

Then in the cell just enter
=E7

I thought about that and would have tried it eventually.

The possible advantage is the number value generated from the E7 value
is still a number and can be used in further calculation if required.

I don't understand. Why would I not just use E7 in any further
calculations?

 

[Roger Govier]

Hi

I don't understand. Why would I not just use E7 in any further
calculations?

In your particular scenario, I agree.
I was giving the generalised case of why it may be of advantage. The
cell could be carrying a formula with some sort of lookup, and the
resulting value could then be used as part of further calculation,
without recourse to repeating the lookup.

 

[Gord Dibben]

What Ron meant was if you entered Max's formula of

="Cost at "&TEXT(E7,"$#,##0.00") &"/hour"

You could not use that cell in calculations.

If you custom formatted a cell as Ron suggested, the cell could be used in
calculations because it is not text.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

And I meant "Roger" not "Ron"


Gord

What Ron meant was if you entered Max's formula of

="Cost at "&TEXT(E7,"$#,##0.00") &"/hour"

You could not use that cell in calculations.

If you custom formatted a cell as Ron suggested, the cell could be used in
calculations because it is not text.


Gord Dibben MS Excel MVP

Gord Dibben MS Excel MVP

 

[Roger Govier]

And I meant "Roger" not "Ron"
That's Ok Jim <vbg>

 

[Gord Dibben]

I did it to Niek and Nick a couple of times lately and I believe to yourself and
Ron more than once.

At least in my condition I get to meet new people every day, although calling my
current spouse by first spouse's name doesn't garner any brownie points, I'll
tell ya<g>


Jim

That's Ok Jim <vbg>

Gord Dibben MS Excel MVP

 

[LurfysMa]

Hi

In your particular scenario, I agree.
I was giving the generalised case of why it may be of advantage. The
cell could be carrying a formula with some sort of lookup, and the
resulting value could then be used as part of further calculation,
without recourse to repeating the lookup.

Ok. If I needed E7*E9 + G7*G9 or some such rather than just E7. Right?
Good point, thanks.

 

[Max]

You're welcome, LurfysMa !

For more examples, browse Debra's nice page at:
http://www.contextures.com/xlCombine01.html
Excel - Combining Data