Combining text and cell reference keeping number format

[Guest]

Dear All,

I have a spreadsheet with a value of 123.889977 in cell A5

I have formatted it to currency and 0 decimal places so it looks like £124.

This figure is derived from a sum.

In cell C3 I have ="Total shortfall is "&A5
but it looks like this... Total shortfall is 123.889977

- How would I edit the formula to keep the formatting so that it reads
Total shortfall is £124 ?

Many thanks

Rajen

 

[Guest]

try
="Total shortfall is "&text(A5,"£.00")
I assume the £ is the appropriate format character. I am familarr with $ in
this situation.

 

[papou]

Hello Rajen

="Total shortfall is " & TEXT(A5,"# ##0 £")

HTH
Cordially
Pascal

 

[Guest]

Thank you very much.

That's pretty cool.

They both work, however the £ sign needs to be at the front when using the
##'s.

Thanks both of you

Rajen

 

[David Biddulph]

If the custom format of cell A5 is £#,##0 and you want to retain that, then
change your C3 formula to
="Total shortfall is "&TEXT(A5,£#,##0)

 

[David Biddulph]

How many decimal places does that give, bj?

 

[Guest]

The wrong number, which is what happens when you don't really read the
question