Can't overcome formatting in unlocked cell

Blank_Cell

New Member
Joined
Oct 31, 2009
Messages
8
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.

:confused:
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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.
 
Upvote 0
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.



:confused:

“Character consists of what you do on the third and fourth tries.”
-- James A Michener
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top