number format with the flexibility to align

[wannabe12]

Hi there,

I have this number format - _-* £#,##0_-;[Red]* £(#,##0)_-;_-* "-"??_-;_-@_-

however I cant align left, centre or align right from the buttons on the ribbon.

Is there a way that the above format could be altered to allow me to use the alignment buttons
Also is there a way that when a cell is blank (i.e. no value) it displays a dash


many thanks.

 

[ClaireS]

Take out the [star]-[space] characters from each part of the format. I imagine you have adapted the original Accounting format to put the £ sign beside the currency amount, but that [star]-[space] is there to pad out the distance between the left-aligned accounting format £ sign and the actual numbers. You don't want multiple spaces in your version: that's what is preventing the alignment from making any difference - your cell is full of spaces!

AFAIK you can't display blank cells as dashes, but you could quickly fill them by select the range then do a Replace of nothing with -. Or if they are the result of a formula that produces a null text string "", change that to a zero and your number formatting should do the rest.

 

[wannabe12]

Take out the [star]-[space] characters from each part of the format. I imagine you have adapted the original Accounting format to put the £ sign beside the currency amount, but that [star]-[space] is there to pad out the distance between the left-aligned accounting format £ sign and the actual numbers. You don't want multiple spaces in your version: that's what is preventing the alignment from making any difference - your cell is full of spaces!

AFAIK you can't display blank cells as dashes, but you could quickly fill them by select the range then do a Replace of nothing with -. Or if they are the result of a formula that produces a null text string "", change that to a zero and your number formatting should do the rest.

Thanks for your response!
Would the format then be this _-#,##0_-;[Red](#,##0)_-;_-"-"??_-;_-@_-

 

[ClaireS]

Thanks for your response!
Would the format then be this _-#,##0_-;[Red](#,##0)_-;_-"-"??_-;_-@_-

Well, that gets rid of your £ sign too. I would suggest this:

_-£#,##0_-;[Red]£(#,##0)_-;_-"-"??_-;_-@_-

Or if you don't need the "gap-the-size-of-a-minus-sign" then :

£#,##0;[Red]£(#,##0);"-"??;@

(very nearly one of the built-in currency options, but gives you the dash for a 0).​

_{^{<strike>
</strike>}}​

 

[wannabe12]

Many thanks!