Excel will not change a format-reverts to Spe

Dell / Optiplex 170l
August 20, 2009 at 13:47:59
Specs: Microsoft Office Excel 2003 SP3, 2.992 GHz / 2045 MB
Similar to query from 'aphil48' on May 17, 2009.
Excel will not change the format of selected cells. I am trying to change some cells to a Custom format (000, 0000, & 00000) and they keep reverting to 'Special' formats. The Custom 000 reverts to Special Chinese (Taiwan). The Custom 0000 reverts to Special Codigo Postal and the Custom 00000 reverts to Special Zip Code.
In your solution to aphil48 you mentioned "The issue was that there was some sort of non-printing character trailing each value which was forcing the values to hold onto the General format." How do you check if there are non-printing characters??

See More: Excel will not change a format-reverts to Spe

Report •

August 20, 2009 at 15:47:12
You can use the =LEN() function to check how many characters are in a cell:

ABC123 should return 6
ABC 123 should return 7 because a space is a character.



Report •

Report •

August 20, 2009 at 19:56:53
Try this:

- Select an empty cell
- Hit Ctrl-c to copy it
- Select the cells you are trying to fomat
- Do Edit...Paste Special...Operation...Add

Sometimes adding 0 to a cell (or multiplying it by 1) forces Excel to convert it to a number which can then be formatted.

I suggest you make a copy of your workbook before trying any of these suggestions in case things go terribly wrong.

Report •

Related Solutions

August 25, 2009 at 11:31:48
I have noticed this happens only on cells which have "Number stored as text" -- this normally generates a green triangle in the top left corner of each cell and an exclamation mark when the green triangle is moused-over.

To fix, click the exclamation mark, then convert to number.

Now when you select custom 000 etc… the format will keep after the next cell has been selected.

Report •

Ask Question