Oct 24 2020 12:14 AM
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.
Oct 24 2020 02:07 AM
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.
Oct 24 2020 02:07 AM
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)
Oct 24 2020 02:13 AM
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
Thank you for your understanding and patience
Nikolino
I know I don't know anything (Socrates)
Aug 29 2023 01:58 PM
Aug 30 2023 04:19 AM
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().
Aug 30 2023 08:29 AM - edited Aug 30 2023 08:30 AM
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 |