| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to copy a number into a text cell, keeping leading zeros? | Charts and Charting in Excel | |||
| combining cells and keeping decimal format | Excel Discussion (Misc queries) | |||
| Making cell reference absolute makes cell format text | Excel Worksheet Functions | |||
| Time format to number while keeping value | Excel Worksheet Functions | |||
| Combining cell reference with text | Excel Discussion (Misc queries) | |||
Linear Mode
