Combining text and cell reference keeping number format

's Computer Specifications

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

's Computer Specifications

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

's Computer Specifications

Hello Rajen

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

HTH
Cordially
Pascal

"RajenRajput1" <(E-Mail Removed)> a écrit dans le
message de news: 85F69DF2-E59A-48D7-82D2-(E-Mail Removed)...
> 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

's Computer Specifications

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" <(E-Mail Removed)> a Ã©crit dans le
> message de news: 85F69DF2-E59A-48D7-82D2-(E-Mail Removed)...
> > 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
>
>
>

's Computer Specifications

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" <(E-Mail Removed)> wrote in message
news:85F69DF2-E59A-48D7-82D2-(E-Mail Removed)...
> 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

's Computer Specifications

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

"bj" <(E-Mail Removed)> wrote in message
news:BEF633EE-5637-48E8-BFBE-(E-Mail Removed)...
> 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

's Computer Specifications

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" <(E-Mail Removed)> wrote in message
> news:BEF633EE-5637-48E8-BFBE-(E-Mail Removed)...
> > 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
>
>
>

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
export teble to text keeping number format	Yossi evenzur	Microsoft Access External Data	2	5th Nov 2009 03:31 PM
How to copy a number into a text cell, keeping leading zeros?	=?Utf-8?B?QmFzaGVyIEJhdGVz?=	Microsoft Excel Charting	2	23rd Feb 2008 03:26 PM
Making cell reference absolute makes cell format text		Microsoft Excel Worksheet Functions	2	22nd Sep 2006 04:47 PM
Combining cell reference with text	mattflow	Microsoft Excel Misc	5	5th Dec 2005 09:32 PM
Convert text string in format of cell reference to a cell reference	=?Utf-8?B?RnJhbms=?=	Microsoft Excel Misc	1	16th Dec 2003 08:52 AM