Solved Excel will not change a cell format.

May 17, 2009 at 05:25:32
Specs: Windows Vista or XP
Excel will not change the format of selected cells. I cut and pasted a table that I sorted on line. The problem after the import is that Excel will not change the format of selected cells. I am trying to change some cells to a number format and others to a time format (h:mm). The only way it will accept the change is to clear the cell and retype it, There are too many numbers to make that fix acceptible. I have also tried the "special paste" multiply by 1 trick, I have also saved the imported file as text and import the text file, but still have the same problems.

See More: Excel will not change a cell format.

Report •


✔ Best Answer
May 21, 2009 at 11:04:12
FYI...I supplied a solution to the OP via email after he sent me a copy of the spreadsheet.

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.

The solution was to use =LEFT(A1,LEN(A1)-1) to strip off the offending character.

I tried both CLEAN and TRIM but neither of them stripped off the character, so I went with LEFT and that seemed to do the trick.



#1
May 17, 2009 at 08:44:29
Typically when something like this happens, the data is stuck as text and won't let go of that format.

If you'll allow me to PM an email address to you, or post an extract of the spreadsheet on line, I'll take a look at it.


Report •

#2
May 17, 2009 at 11:21:59
You can also save the file as a .csv under a new name, then open the .csv file and save it as .xls. The trick is that .csv files do not convey format information so you're resetting all of the formats for the entire spreadsheet this way.

Report •

#3
May 17, 2009 at 18:59:48
I saved it as a .csv file then opened it, but it still did not allow me to change the cell format. The cell is a simple integer, 4. I tried to change it to a number with 2 decimal places, but it still only showed the whole number of 4. If I delete the number and retype the 4 and enter, then it will show the 2 decimals.

Report •

Related Solutions

#4
May 21, 2009 at 10:28:58
Did you get a fix for this?

Report •

#5
May 21, 2009 at 11:04:12
✔ Best Answer
FYI...I supplied a solution to the OP via email after he sent me a copy of the spreadsheet.

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.

The solution was to use =LEFT(A1,LEN(A1)-1) to strip off the offending character.

I tried both CLEAN and TRIM but neither of them stripped off the character, so I went with LEFT and that seemed to do the trick.


Report •


Ask Question