Need to be able to format a calc sheet to have a certain column width by charactor. Can this be done? Have done it in excel just can’t see how to do it here.
Thank you.
I read your question as you may want to set the column to be for example 20 chars wide.
But as we now usually deal with proportional writing, where a letter m is wider than i, this can’t be done easily. Font-Designers can put m-width etc. in the font-Data, but I never tried to access this from Calc…
So this rises some questions:
What unit Excel is using for this?
Are you using proportional writing?
Is the column expected to adapt, if you change the font?
1 Like
Here is a fine explanation on Stack overflow and possibly some VBA that OP can convert to a macro.
But what is the point (other than 1/72 of an inch of course)?
As an adaptable workaround, set two auxiliary rows (1 and 2 in my sample). The number of characters width in one, and the =REPT("x";A1) formula in the other. Then you can select the cells in second row and set the Optimal Column Width.
You can set left and right cell border padding to 0.
The final outcome is to create a space formated file as in a .prn file in excel. Another program uses the exact location of columns to locate data for processing.
Will this “lock” the column to that number of charactors, even if subsequent data overruns that column size?
The final outcome is to create a space formated file as in a .prn file in excel. Another program uses the exact location of columns to locate data for processing.
So you have a qualified table with structured(?) data, in an Office-Program.
Now - instead of using XML-export or JSON you want to “print” the data,
so another program can search/find this data at a certain position in this output.
Sorry, but IMHO this seems to be a waste of time.
And for me it seems you need to export to a txt-file instead of printing
Right,
Don’t need to print it just to save it as a file with those conditions. Any way to build in an extension?
The idea is to create a space formatted file with a predetermined amount of charactor width.
Somethig like:
Name a Date aAddress a
Other name Date bSecond address
Another namDate cOther address
?
Yes,
But how to set the column width by the number of charactors ahead of time.
Sorry,
Little slow on the uptake… Is that what you did using the =REPT(“x”;A1) Formula? Seems like it should be built in… But then again it is Freeware…
Maybe you can fill all the data, export as csv with fixed column width, then import this csv with fixed width, deleting the columns with excess data (shown in red in the screenshot).
So, if LibreOffice Calc is unable to set column width by number of characters, how many characters does each column hold?
Exactly as many, as fits into the given width, using the given font. You may even count them.
If people imagine, that the “character width” in Excel helps to understand how many characters fit into any cell - they are mistaken. Decimal and thousand separators break that calculation. Percents and currency symbols break the calculation. Changing font for cells breaks the calculation. Use of fonts with uneven digit size breaks the calculation. Use of non-decimal numbering system … you guessed it: breaks the calculation. It only helps one thing: to create greater confusion.
Excel can’t really do this too.
What is actually seems to use is the width of 0 but a column wich fits
"00000" may not fit other strings like
"MMMMM" unless you force not proportional font (as I did). So prepare for this:
00000
MMMMM
.
If you still insist of using this wrong approach, use the work-around shown by @LeroyG above and use 0 instead of x.
We live in modern times and UTF8 has variable bytes per character, fonts are proportional (and there are even smart fonts, wich can change appearance on rendering) and your cell may hold also some chinese characters so programming is now much more complicated than in 80x25 lines of shell-window/screen.
1 Like
I suppose that I (and some others) are confusing the fields of a spreadsheet with the fields of a database. Speaking (writing?) of a database, is Libreoffice’s database module installed separately from the other modules? When click File, New, I don’t see an option for a new base file. (I have no use for the module, I’m just curious.)