Combining text and cell reference keeping number format
 

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

Combining text and cell reference keeping number format
 

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


"RajenRajput1" wrote:

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

Combining text and cell reference keeping number format
 

Hello Rajen

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

HTH
Cordially
Pascal

"RajenRajput1" a écrit dans le
message de news: ...
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

Combining text and cell reference keeping number format
 

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

"papou" wrote:

Hello Rajen

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

HTH
Cordially
Pascal

"RajenRajput1" a écrit dans le
message de news: ...
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

Combining text and cell reference keeping number format
 

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

"RajenRajput1" wrote in message
...
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

Combining text and cell reference keeping number format
 

How many decimal places does that give, bj? :-)
--
David Biddulph

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

"RajenRajput1" wrote:

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

Combining text and cell reference keeping number format
 

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

"David Biddulph" wrote:

How many decimal places does that give, bj? :-)
--
David Biddulph

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

"RajenRajput1" wrote:

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