Insert cell reference into text string 1

[OMoore]

Hi all,

Is it possible to insert a cell reference in between a text statement in one cell?

for example, the following all in one cell:

There is spare capacity of =C19 pieces between both banks of machines.

Where the resultant is:

There is spare capacity of 2005 pieces between both banks of machines.

I realise I can do this by having two individual cells with the answer in it's own cell, bnt I've always wondered if the above is possible.

Thanks
Owen

 

[xlbo]

="There is spare capacity of " & C19 & "pieces between both banks of machines."

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[OMoore]

Great Geoff.

I am now getting a decimal point in my answer but I need whole numbers. I have cell C19 formatted with 0 decimals.

Owen

 

[xlbo]

="There is spare capacity of " & text(C19,"0") & "pieces between both banks of machines."

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[OMoore]

Geoff - you're the man!!

Owen

 

[xlbo]

Thanks - as a general rule, a & can be used to join information together into a text string.

These bits of information can take the form of:

A constant - eg "This is a constant text string"
A formula - eg SUM(A1:A100)
A Cell Ref - eg C19

So you can either do your formulae in the cell you want to reference or even in the holder cell. If C19 held the formula =SUM(A1:A100) then you could use

="This is the total for range A1:A100 " & C19

OR you could use

="This is the total for range A1:A100 " & SUM(A1:A100)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[OMoore]

Good to know. I can see lots of applications for this.

Owen