Excel Rejects a my Custom Number Format

[John1987]

Good Day. This may be a simple error on my part, but I am not an expert so I seek help. 

I have custom formatted a number on a worksheet to look like this: 1,100 Sq.m. using the custom number format (#,### "Sq.m") . After saving and re-opening it, I opted to repair the file but the formatting is gone. Is this a setting problem?

Ho do I fix this? Thanks in advance.

[#2]

@John1987 

Check the attached Workbook on the Sheet 1. 

I've applied the Custom Number Format  #,###0 "Sq.Mt",   and its working. Even I've saved & closed the file also quit from Excel and reopen the WB, found as it was.

N.B.

• I've applied these also #,### "Sq.m"   and (#,### "Sq.m"), check column D, F & H.
• I've used this on Excel 2013 & 2016.

[#3]

@John1987 

With your permission, if I can recommend you, add a file (without sensitive data) to your project.

Explain your plans in relation to this file. So you can get a solution that is tailored to your needs much faster.

At the same time, it is much easier for someone who wants to help to understand the subject.

A win-win situation for everyone.

Please no Picture, even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases.

* Knowing the operating system would also be an advantage.

Thank you for your understanding and patience

Nikolino

I know I don't know anything (Socrates)

[#4]

@John1987 

However, here is some information on Excel for web via user-defined number format.

Excel provides many options for displaying numbers in different formats like percentages, currency, and dates. If the built-in formats don’t work for your needs, you might want to create a custom number format.

You can’t create custom formats in Excel for the web but if you have the Excel desktop application, you can click the Open in Excel button to open the workbook and create them. For more information, see Create a custom number format.

Create a custom number format

https://support.microsoft.com/en-us/office/create-a-custom-number-format-78f2a361-936b-4c03-8772-09f...

Thank you for your understanding and patience

Nikolino

I know I don't know anything (Socrates)

[#5]

I found an excellent work around to the Custom Formula Problem for the Web Version of Excel. Simply use the TEXT() formula. I wanted the value '1' (a numerical value) to be displayed as "001". Therefore I put the following formula in the cell '=Text("1", "000")' and viola it displays the custom format. Funny how the functionality remains in the web version but developers could unlock this feature. Maybe there are some more difficult number formatting that cannot be done with the Text () formula.

[#6]

@Crendent 

The only now you have text instead of number. Try SUM() on such cell(s), it returns zero.

All custom number formats you may apply to the cell are supported by TEXT().

[#7]

@Sergei Baklan 

I am aware of this potential problem and it did not exactly arise when I used that formula. I figured it was handled since you can still add a number to the text and it's smart enough to recognize that you are looking at a number. I mostly was using it for generating ID's that were unique so I didn't need to sum them. Adding did work on individual cells though.

I think the work around for adding a sum is to create a separate column with the numerical values, then sum that column. It is unfortunate that such widely used and valuable software cannot get simple maintenance and attention from the developing team.

Text Column	Math / Number Column
001	1
002	2
003	3
004	4
005	5
006	6
021	21
=TEXT(1+D18,"000")	=NUMBERVALUE(D19)
	Hide this column