Can't overcome formatting in unlocked cell

[Blank_Cell]

Sheet in 2010 won't accept Accounting format. But it's not locked and not protected against formatting.

Data appears left flush with no symbols. When new rows or columns are inserted, won't format these either.

Other sheets in workbook are fine.

Would appreciate any suggestions.

 

[JoeMo]

The left alignment suggests that cell is formatted as text. Copy any empty cell then right-click the problem cell, choose paste special and under "Operation" choose Add and click OK. That should produce a number format which can be converted to Accounting format.

 

[Blank_Cell]

The left alignment suggests that cell is formatted as text. Copy any empty cell then right-click the problem cell, choose paste special and under "Operation" choose Add and click OK. That should produce a number format which can be converted to Accounting format.

Tried this, copying from empty cell on problem worksheet, then from empty cell in new workbook. Formatting in anything other than TEXT still was not permitted.

Also tried using PASTE SPECIAL to bring over number in Accounting format from another sheet in same book. Instantly converts to left-flush bare number.

Maybe my question should be: "What would create TEXT formatting that cannot be reformatted?

I would think the problem is with locking or protecting but my worksheet is unlocked according to the FORMAT CELLS dialogue box and I am still seeing the PROTECT option in REVIEW tab which I understand to mean that the options for Formatting protection have not been activated.

Open to all thoughts.





“Character consists of what you do on the third and fourth tries.”
-- James A Michener

 

[Jerry Sullivan]

Maybe my question should be: "What would create TEXT formatting that cannot be reformatted?

Open to all thoughts.

Might the cell be using Conditional Formatting? If the CF rule evaluates to True, that would override any number formatting applied directly to the cell.

 

[Blank_Cell]

Thanks so much, Jerry

It wasn't CF but out of sheer stubbornness I found something that works:

Opened a new sheet, copied over some of the unformattable cells to work with and to my delight, they were formattable.

Still have no idea why the previous sheet couldn't be formatted -- if anybody has a thought, would like to learn so I don't get in same stuck situation again.

Thanks all

 

[Jerry Sullivan]

Still have no idea why the previous sheet couldn't be formatted -- if anybody has a thought, would like to learn so I don't get in same stuck situation again.

One other possibility...were you trying to apply the Accounting formatting using a Button on the Home Tab of the Ribbon?

Those buttons don't directly modify the NumberFormat property of the cell, but instead they apply Cell Styles ("Currency", "Percent" and "Comma") to the cell. That typically gives the same appearance as if the Number Format were changed through the Format Number dialog; however it's possible to modify Cell Styles to have different properties for Number Format, Font, Color, etc...

So it's possible to modify the "Currency" Style (which has the tooltip "Accounting Number Format") to display "Text" NumberFormat.

Since you said the other sheets in the same workbook worked fine, then this possibility is a bit of a longshot.

It's possible that you used a Button on the Ribbon on the problem worksheet and used the Number Format dialog or a different Style Button on the other sheets.

Thought this was worth noting for others who find this thread when having trouble applying Accounting NumberFormat through their entire workbook.