Forum Discussion

John1987

Copper Contributor

Oct 24, 2020

Excel Rejects a my Custom Number Format

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...

Excel for web

Crendent

Copper Contributor

Aug 29, 2023

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.

SergeiBaklan

Diamond Contributor

Aug 30, 2023

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().

Crendent
Copper Contributor
Aug 30, 2023
SergeiBaklan

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

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