How to imbed a cell reference in a text field?

  • Thread starter Thread starter LurfysMa
  • Start date Start date
L

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
 
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.
Click to expand...

I found the answer. Use the concatenate function:

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

Thanks
 
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


LurfysMa said:
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.
Click to expand...

I found the answer. Use the concatenate function:

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

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

Thanks.

That's the type of solution I was looking for, but couldn't find, when
I stumbled across the Concatenate function.
 
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
Click to expand...

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.
Click to expand...

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

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.
 
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
 
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
Click to expand...

Gord Dibben MS Excel MVP
 
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>
Click to expand...

Gord Dibben MS Excel MVP
 
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.
Click to expand...

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