I have some data in Excel. The font is Courier New, 9 pt. because if I define a column width, that font fits the width. For example, if I define a column width as ‘20’, and I enter ABCDEFGHIJ1234567890, the text fits in the defined width. This is very useful to me.
In this particular file, I define the font and enter a width. But in a cell that should be 40 bytes wide, defining a width of 40 yields a cell of 34.5 width. A 30-wide cell only fits 26 characters. A 20-width cell doesn’t quite fit 17 characters. I get around this by entering the example text in the first paragraph and dragging the right border to the end of the text. But it would be easier if the cell is the width I tell it to be.
Under “Format Cells”, “Alignment”, “Text Control”, there is a check box called “Shrink to fit”. If it’s checked, the font size should change to fit the column width. It it’s not checked, the font size shouldn’t change at all, unless there’s some background default that I’m not aware of.
Nothing is checked. But I don’t want the text to shrink to fit the cell. I want the box to be a defined width that is the same as the maximum number of characters in 9 pt. Courier New. If I define a column to be 30 units wide, then I know I can put 30 Courier New 9 pt. characters in that width. It works on every other file, but not this particular one.
Maybe the default font for the default theme in that file is different, and that is affecting the column width. I just tested that out with Excel 2013 and changing the theme font changes the column width even though the column font stays the same.
I’m having a little trouble understanding what it does that you don’t want. Are you saying the column stays the same width, but not as many characters of your chosen font size fit into that width in this file compared to others?
For every other spreadsheet, if I define a column as 30 units, then I can fit 30, 9 pt. Courier New characters in the cell. 20 units width, 20 characters. And so on. With this file, a 30 unit wide cell only fits 26 characters. A 20-unit cell doesn’t quite fit 17 characters. I know the maximum number of characters allowed in a column. I know that 9 pt. Courier New has a one-to-one relationship with the column widths. So I type ABCDEFGHIJ12345678901234567890 into a cell (alphas because Excel is too stupid to know I want a string of numbers unless I define a cell as text, or thinks it’s smarter than I am and knows I don’t want a string of numbers). Then I highlight the column and stretch it to the last character. Now I know that my cell width is the maximum width of my data.
This is part of what the Help section says, “…you can specify a column width of 0 (zero) to 255. This value represents the number of characters that can be displayed in a cell that is formatted with the standard font…” I’m not sure what the “standard font” is, but it’s possible it’s set differently for this file than for others.
It is also possible to change the column width unit definition to inches (or centimeters) rather than characters, but it doesn’t sound like this is something you want.
I was able to recreate the issue by changing the theme font to something that looked a little bit narrower, I think this may be it. Microsoft has some info about how the column width is determined that verifies much of this.
One other option… You can select one or more columns and choose “Auto fit columns”, and the width will change to fit the longest string of test within that column.
The trouble is that a lot of data comes in with more characters in a cell than are allowed. If we just widen the columns to fit the data, or if we us a font other than 9 pt. Courier New, we might have 31 or more characters in a field that is only allowed to have 30.
Here’s how it works: We receive an Excel file. I edit the file (make formats standard, make sure everything fits into the allowable space for each field) and save it as comma delimited. The .csv file is imported into Access and exported as a fixed-width text file. Anything that is outside of the defined parameters is truncated. The fixed-width text file is used by Easytrieve to do the reformatting and calculations.
No, never did. Since this is the only file this happens with, I suspect there’s some sort of ‘global formatting’ that’s switched on. Like ‘Ignore what the User wants. You know better than the Human’ is checked, or something like that.
In my version of Excel, it’s on the Page Layout tab, all the way on the left, “Themes.” “Office Theme” is the normal default. The Theme Font is also there if you don’t want to mess with the overall theme, mine is Calibri.