number format with the flexibility to align

wannabe12

New Member
Joined
Nov 28, 2012
Messages
13
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.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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.
 
Upvote 0
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)_-;_-"-"??_-;_-@_-
 
Upvote 0
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>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top