In Excel, Format a Number with Intermediate Spacing AND Optional Decimal

Question

I have a series of numbers in Excel that I would like formatted in a certain way and I need some help.

Background:

I work in construction, and am using the Construction Specification Institute's (CSI) Masterformat system of categorizing building materials used in a project. This standardized system works as follows: any type of construction material or assembly is given a 6-digit (or occasionally, 8-digit) code. These digits are structured in a way where the first two digits indicate the overall category of material type (e.g. concrete, metal, masonry, etc), and you get more and more specific as you go down the remaining digits in pairs.

For example, 03 52 16.13 indicates Lightweight Cellular Insulating Concrete, with the parts being broken down as follows, each progressively getting more specific.

03 00 00 is Concrete

03 52 00 is Concrete Insulation

03 52 16 is Lightweight Insulating Concrete

03 52 16.13 is Lightweight Cellular Insulating Concrete

As you can see, these numbers have a specific formatting that is important to maintain. The rules are as follows:

• RULE 1 — A number must be either 6 digits (e.g. 03 52 16), OR, 6 digits followed by two decimal digits (e.g. 03 52 16.13)
• RULE 2 — Digits must be spaced after every pair. (03 52 16 is correct, 035216 is not)
• RULE 3 — For numbers beginning with 0, the leading zero must be maintained. (03 52 16 is correct, 3 52 16 is not)
• RULE 4 — Numbers that are six digits only may not have decimal zeroes at the end. (03 52 16 is correct, 03 52 16.00 is not)

Which brings me to my question:

I have a long list of these numbers in an Excel table for my project, which are entered manually (not via formula). How can I set up a universal cell formatting that allows for this?

Examples:

• Typed 12345 -------> Should Become 01 23 45
• Typed 123456 ------> Should Become 12 34 56 (NOT 12 34 56.00)
• Typed 123456.78 ---> Should Become 12 34 56.78 (NOT 12 34 56)

For cell formatting...

00 00 00 .00 would satisfy Rules 1+2+3, but violates Rule 4.

00 00 00 .## would almost satisfy all four rules, but would leave a strange decimal at the end.

What to do? Is there a way to do this without conditional formatting? Thank you.

Answer 1

The precise answer is NO. You cannot do all the required things using only the numerical value entered and the normal, English version, custom number formatting tools.

You could achieve it using either Conditional Formatting, which you have asked not to do. In fact, that is the classic way to deal with needing a decimal point sometimes, yet not wanting it when there are no decimal places.

You could also achieve it a few ways using a helper column in which formulas prepare a text version of the numbers.

So, if, for example, you can maintain a not-perfectly-formatted list somewhere that can be referred to by formulas, one in which the form "03 52 00." can occur because no one sees it, and use formulas in the presentation to users of the codes, with the codes then being text results presented using IF and TEXT using two different formatting strings for the two possible IF results (so if the IF detects no decimal, then TEXT would present using ## 00 00 00 while if it sees a decimal is needed, TEXT would present using ## 00 00 00.##.

(I've included the "## " at the start of each because it seemed you were saying eight digits could occur, with me taking that as before any decimal point but reading it a couple times more makes me think you mean "6" or "6.2" with the latter being how "eight" comes into play. So I've provided them "in case" but drop those three characters from the formatting strings if "eight" means "6.2" and never "8".)

The helper table and text outputs could be a problem if one planned to edit these codes in place, perhaps by inserting and deleting them. That seems very unlikely though, so it ought to be suitable. And a separate data table that is where actual maintenance occurs seems much more desirable. If not your choice, then not your choice — it's YOUR spreadsheet, not mine — but it seems more desirable.

Honestly though, given that there'd be a single rule to cover one range of cells, and while that range might be thousands or even tens of thousands of entries, it is hard to see how Conditional Formatting could be in any way beastly. Again though, YOUR spreadsheet, not mine, so your choices.

But again, while there are certainly ways to get your desired output appearance, there is no way, aside from Conditional Formatting or a macro that operates every time one of these cells is selected and then exited from, analyzing the content and formatting it one way or the other, to get your desire from any built-in number formatting string in the English version of Excel. People have lamented this for over 30 years.

One final thought that you might look into, if you are adamant about the goal using only built-in number formatting tools, is that Excel allows you to use number formatting tools for different languages/national cultures. For instance, choose "Japan" and you find a variety of date formatting tools in there that are not mentioned on any of the "usual suspect" websites. They include ways to indicate a reign and the year thereof, not necessarily useful to any not-Japanese use, but also some different letter codes for the parts of dates and those seem to work in English Excel as well, sometimes with odd effects that might be useful. So you could search one after the other either inside Excel, or on the internet, or both, and perhaps find a culture for which MS HAS created a formatting string tool that will suppress the decimal point as you need here.

I'm not offering the last as anything I'd wish upon a fellow human, but you know... not only would it bring you happiness herein, but if you then shared it with us all, you'd've solved one of the oldest aggravations in Excel.

Answer 2

Something to try, maybe not perfect, but might work well enough:
(This was created in Libreoffice Calc 6, so might differ slightly in Excel)

Apply formatting by CTRL-1 and set it to be 00 00 00.##,
and also set align right on the cells.

Then to see faulty items clearly, set Conditional formatting to be
=NOT(OR(LEN(TEXT(C1,"000000.##"))=6,LEN(TEXT(C1,"000000.##"))=9))
(set it by Menu: Format > Conditional > Condition in Libreoffice 6 Calc)

---

Example display created by the *.csv below.

---

Save this as special-format.csv using any text-editor and then open it using OpenOffice Calc (where it was made) or Excel.

12345,"=TEXT(A1,""00 00 00.##"")",=A1,"=LEN(TEXT(C1,""000000.##""))","=NOT(OR(LEN(TEXT(C1,""000000.##""))=6,LEN(TEXT(C1,""000000.##""))=9))"
123456,"=TEXT(A2,""00 00 00.##"")",=A2,"=LEN(TEXT(C2,""000000.##""))","=NOT(OR(LEN(TEXT(C2,""000000.##""))=6,LEN(TEXT(C2,""000000.##""))=9))"
123456.7,"=TEXT(A3,""00 00 00.##"")",=A3,"=LEN(TEXT(C3,""000000.##""))","=NOT(OR(LEN(TEXT(C3,""000000.##""))=6,LEN(TEXT(C3,""000000.##""))=9))"
123456.78,"=TEXT(A4,""00 00 00.##"")",=A4,"=LEN(TEXT(C4,""000000.##""))","=NOT(OR(LEN(TEXT(C4,""000000.##""))=6,LEN(TEXT(C4,""000000.##""))=9))"