Why won't excel apply my custom cell formatting?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Scott - 30 May 2006 14:18 GMT

I have a column in my spreadsheet with a number that is 7 characters long. I
want to creat a custom format for these cells to make them 10 characters long
and so I created a custom setting with 0000000000 and that usually works. It
normally places the extra 3 zeros in front of the 7 characters but on this
spreadsheet it only does it to one of the cells in that row. As far as I can
tell, there is nothing different about that cell. Any ideas?

Thanks!

Reply to this Message

David McRitchie - 30 May 2006 19:44 GMT

Hi Scott,
It make a difference if the cells are numbers or text before you provided a
custom formula. Use the builtin function ISNUMBER or ISTEXT or ISBLANK
to make a determination. If you switch between number and text, or between
text and number you MUST reenter the values.

Several methods of reentry exist, basically by retyping, with a use
of special values, or with a maccro.
See the TrimALL macro in
http://www.mvps.org/dmcritchie/excel/join.thm#trimall
which I find the easiest because the macro is readily available in my menus
and works from a selection.

Another way to effect a reetry after changing the format is to select an emtyp
(never used cell) and copy it (Ctrl+C), then select the area that you changed
the format of, and then use Edit, Special Cells, Add
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

> I have a column in my spreadsheet with a number that is 7 characters long. I
> want to creat a custom format for these cells to make them 10 characters long
[quoted text clipped - 4 lines]
>
> Thanks!

Reply to this Message