Ask Your Question

How can I format a number in a merged writer file

asked 2021-04-11 21:38:57 +0200

denduich gravatar image

updated 2021-04-12 19:19:53 +0200

ajlittoz gravatar image

My goal: Merge a product database (from a Calc sheet) (with descriptions, prices, etc... and about 200 lines/products) to produce in a Writer file individual stickers for each product automatically and with a good looking (not standard) formatting. Each text placed in the right format at the right place on the resulting sticker.

I use frames to format the stickers to enable the right placement and formatting for each text and price. Each text can vary in size. I can format well the text resulting from the merge as wished. But numbers have not the right number of digits after the coma and I cannot find how to edit it automatically. I need to pass through all products to correct it individually. Is it possible to fix it automatically? I take three examples: 1) a price of CHF 3,00 appears with 3 in the merged sticker. 2) a price of CHF 3,50 appears as 3.5 in the merged sticker. 3) a price of CHF 3,55 appear as 3.55 in the merged sticker. I'd wish to have automatically the right number of zero and the "CHF " appearing in the resulting sticker as it is possible to do in a Calc cell.

I link the Calc data through the creation of a database (based itself on the Calc sheet) and insert by drag and drop the headline of the column I wish to introduce in my merged document. I attach my template for the Writer merge: eti-theier-region-cueillette_2021_V1 and an example of the resulting merge Writer document (including only three products) : essai-pres-t-10



Thanks in advance for your help. LO version: (x64) OS: W10 professional v.1909

edit retag flag offensive close merge delete


Edit your question to better describe your data and procedure. You can also attach sample file(s) to the question (with the paperclip tool). Don't forget to mention LO version and OS name.

I am not convinced that frames are the best tools for the purpose.

Note that this site is not a forum and answers are reserved for solutions. So, please, edit your question.

ajlittoz gravatar imageajlittoz ( 2021-04-12 08:09:41 +0200 )edit

To be able to give a relevant answer, I need to know how you link the Calc data with Writer. In other words, how do you "technically" insert the numbers into Writer? The simplest way is to attach at least your Writer document to your question. Make it short, one page max.

ajlittoz gravatar imageajlittoz ( 2021-04-12 10:13:13 +0200 )edit

Rereading my answer I realise I didn't address your question. I can't guess what the source data looks like. It is passed to Writer as "text". Therefore, there is no editing capability. The format must be worked out in the "database" itself. Either give it an adequate custom format or use a scratch cell to copy the correctly formatted value and force it to "text" so that it will no be further transformed.

ajlittoz gravatar imageajlittoz ( 2021-04-12 19:41:11 +0200 )edit

Your last comment has enabled me to solve my problem. I added a new column in my original Calc sheet with the wanted format and used it to edit my merging document. many thanks.

denduich gravatar imagedenduich ( 2021-04-18 10:36:18 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2021-04-12 19:34:43 +0200

ajlittoz gravatar image

updated 2021-04-18 11:09:02 +0200

Frames are a very bad idea: they are not ordered and they create no relation to database fields. In addition your page would like to display several "database records" but you didn't define breaks between these records.

Your problem is akin to printing labels with Mail Merge. You then have three options:

  • use Base, the DB interface component

    It provides a report generator capable of producing Writer documents. This is perhaps overkill and it is page-based while you'd like to create several labels on a sheet of paper.

  • use the mail merge wizard Tools>Mail Merge Wizard

  • create labels File>New>Labels

In all cases, data source is a "database". But for the last case, it can be a Calc spreadsheet. Your best and easiest try is probably the mail merge wizard because you 'll have the full Writer formatting power at your disposal.

EDIT 2021-04-18

The question was in fact about formatting a computed value to insert it into a Writer document. To avoid any behind-the-scene formatting of numeric values, copy the values into an additional column and force formatting to your desired form (for instance, fixing the number of decimals or imposing text interpretation).

To show the community your question has been answered, click the ✓ next to the correct answer, and "upvote" by clicking on the ^ arrow of any helpful answers. These are the mechanisms for communicating the quality of the Q&A on this site. Thanks!

In case you need clarification, edit your question (not an answer which is reserved for solutions) or comment the relevant answer.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2021-04-11 21:38:57 +0200

Seen: 48 times

Last updated: Apr 18