Written by Allen Wyatt (last updated June 19, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
David wonders if there is a way to format a column to display numbers as spaced pairs [xx xx xx.xx] but only display the decimal and trailing pair of a number if they exist? For instance, 123456 would display as 12 34 56 (no decimal point) and 123456.7 would display as 12 34 56.70. David cannot seem to figure this out using custom formats.
The only way to do this using custom formats is to create two custom formats. The two would look this way:
## ## ## #0 ## ## ## #0.00
This would allow up to 8 digits (10 digits if you have 2 to the right of the decimal point) to be displayed as spaced pairs. The problem is that you would need to look at your data and apply whichever format is appropriate for the data. The only way to make the application of the custom formats automatic, would be to also apply conditional formatting to the cells. Once the custom formats have been defined, apply the second one (the one that allows for digits to the right of the decimal point) to all of the cells. Then follow these steps:
Figure 1. The Conditional Formatting Rules Manager dialog box.
Figure 2. The New Formatting Rule dialog box.
Now your numbers should appear formatted just as you want them to appear.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13771) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
You can, in a macro, specify a custom format for a range of cells. If the custom format doesn't seem to "stick" (so to ...
Discover MoreAdding a custom format to Excel is easy. Having that custom format appear in all your workbooks is a different story ...
Discover MoreWhile the implementation of custom formats in Excel is not terribly robust, you can still achieve some amazing results ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-06-19 10:58:08
Hans-Georg Lindic
Hi Allen,
=(INT(A1)=1) will also do the Job.
Best regards
Georg
2024-06-19 05:40:28
Brian
Surely the formula "=A1=INT(A1)" does the exactly same as "=IF(A1=INT(A1),TRUE,FALSE)" - or have I lost the plot?
2020-06-15 13:17:08
Dave
If you're willing to accept always having a decimal point, this format would work well:
"## ## ## #0.??"
That would get you results like "12 34 56." "12 34 56.7" and 12 34 56.78"
This isn't quite what was asked for, but it may be good enough.
Separately, I might suggest tweaking Allen's formats for conditional formatting to:
"## ## ## #0.00" and "## ## ## #0_._0_0"
This would keep all the digits within the column lined up, and it helps visually differentiate between the numbers with decimals and those without.
2020-06-14 06:58:53
Yokeboon
Use custom format
00 00 00 00 00.00
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments