How to parse text as a number?

English
,
#1

I have searched and I am unable to find anything to fit my needs.

I have a table in Writer, which I believe is equivalent to a calc spreadsheet and can hold functions.
I have had limited success with having active functions in the Writer document table.

I wish for text in a cell to be parsed into a number for display in another cell, for example…

Consider cell A1 to equal text “1+1+1”

I am looking for a function that would equate to the following…

Cell A2 could equal “=PARSE(A1)” and the display would be “3”

Any help? thanks

1 Like
#21

There may be reasons.
I was contributing to different forums / help-sites concerning LibreOffice and/or OpenOffice.org or Apache OpenOffice for about 12 years now, and I also don’t remember a request concerning a similar approach to get a document for entering and adding up time slices by kind of usage.
Was this because @Bonko was the first one to find an especially clever and efficient design for such a document?
I would seriously doubt this. Lots of reasons. And this doesn’t seem to be a “matter of perspective”.

#2

This can’t be done in Writer. Tables in Writer are primarily intended to contain text. Each cell can be considered as a separate sub-document with formatted paragraphs and all sorts of other “objects”. They can however be used as very simplified spreadsheets, rougly limited to addition, subtraction, multiplication and division. This is a courtesy feature.

Anything more complex must be done in Calc and you link your Calc sheet inside your Writer document.

Calc provides a function to do the reverse of your specification: FORMULA() which reports the formula used in the cell designated by the argument. Therefore you must proceed as follows:

  1. write your = 1 + 1 + 1 formula (or whatever formula you have in mind) in cell A2
  2. in cell A1, enter =FORMULA(A2)

You’re done.

1 Like
#3

Thank you for letting me know the answer is no.

My solution is this…

Function SuperAdd(_cell)
lTexts = Split(_cell,"+")
lTotal = 0
For Each lText in lTexts
lTotal = lTotal + CInt(lText)
Next
SuperAdd = lTotal
End Function

Now, all I have to do is put the following into A2…

=SuperAdd(A1)

Thank you!

#4

Note that the FORMULA() function is much more versatile than your macro.

#5

Sure, and maybe I have misunderstood something.

Please explain how FORMULA() can convert the text “1+1+1” of one cell to display “3” in another. Many Thanks.

#6

It works the other way round: you wirte a formaula (as usual) where you want the computation to occur. FORMULA() allows to display this formula somewhere else.

I don’t know the goal of your spreadsheet. If it is intended for educational purpose where a user modifies the cell for the “formula” and you see the computation result, FORMULA is not the ideal tool. With it, you change the formula (in the result cell) and you see the modified formula in the other cell.

#7

I apologise if I was unclear with what I wish to acheive.

A user will enter text into a cell over time. The user may add 1,2, or a greater value to the existing contents of the cell. For example…

Take cell A1, it may contain “1+2+3” as text and is what should be displayed, and on an occasion the user adds “+4” producing a cell text value of “1+2+3+4”

It is desired that cell A2 to equal the sum of numbers entered into cell A1. The function that I have created works great for integers and if CDbl is used it will work for decimal point numbers.

My intial enquiry is to determin whether this functionality already exists.

I have tried my function in a table in a writer document but I cannot seem to get it to work. Any ideas?

Thanks

#8

As already stated, there is no built-in function in Writer to do this.

However, I still don’t understand the goal. It is generally considered “dangerous” to let readers (I use this word to contrast with a collaborative team) modify freely a document which is supposed to be “live” for a long time. In such a case, additions should be controlled with a form. And the form is itself backed by some database application.

If your goal is simply to add numbers, it is simpler to have one row per entry. This way, a user add a new row above the result row, to enter the new number (or amount, or …). The result call is in the very last row of the table and this row contains nothing else than the result itself (in other words, tows above are for “transactions” and the last row is for cumulative total). With this layout, you can have a “generic” formula to add up everything in some column. This is possible in Writer.

#9

Thank you for your clarification that there is no built in function for what I wish to acheive.

You say you dont understand the goal but I am at a struggle with how I can be more concise.

The aim of the table is to produce a total of the totals. For example the last cell of each row holds the value (sub total) of the previously described function and the last row of the table displays the total of these sub totals.

Please explain how I can get a writer table to execute the user defined function. Thanks.

#10

Attach a sample file. Your last statement is much clearer now. I think it is easy to configure your table for a running total and the grand total in the last line.

#11

Please find uploaded my test experiment design populated with sample data.
monthly timesheet.odt (17.9 KB)

#12

Open the form, enter data, run reports.
activity_times.odb (22.4 KB)

#13

A database for such an application is the right way to go. @Villeroy provides a solution for this. If you consider this is too complicated, I made a quick’n’dirty spreadsheet.

The first sheet Summary is the summary with a grand total. Every activity is recorded in its own sheet. I have accounted for 35 detail daily lines. Adapt to your needs.

I didn’t take time to polish formatting. So take it as a starting point. There is no fancy macro because there is no need for it; only standard functions. Using built-in primitives allows you to record your activity as hh:mm:ss. The only tricky point is to force summing cells to hhhh:mm:ss format (with 4 "h"s) so that duration above 24h remain in hours unit instead of “jumping” to dates like Jan 2 1900 4:0:0 if you have a 28-hours total.
monthly timesheet.ods (26.5 KB)

#14

Thanks for your efforts. I really appreciate it.

It is however, far too complicated.

We will stick with our original design and use a calculator to add up the numbers and manually put in the totals into the libreoffice writer table cells.
Thank you.

#15

It is a ready made tiny app. You can use it for the years to come without changing anything, just entering dates, hours and activities.
Forms are made for input as easy as possible.
The tables store the raw data.
Queries compile raw data to meaningful, sorted, calculated, grouped record sets.
Reports produce printable output, similar to Writer documents.
You may change the report design for a more adequate layout.
You may link queries and tables to spreadsheets so they appear in spreadsheet cells.
You may copy queries and tables into text documents or even presentations without changing anything in the database itself.

#16

In any branch, any job, any human activity, the introduction of a new tool requires adaptation of mind and procedures. The first step is to learn hwo to use the new tool. The second step is to review present workflow to see where potential conflicts lie. You have then to modify both the workflow and the new tool intended procedure.

“It’s too complicated” is usually an excuse to avoid the pain of training. But this makes the next change much more difficult because you’d have no choice at this time because your present procedure won’t work at all. The disruption will be even more dramatic.

#17

Thank you for your efforts, much aprpeciated.

My perspective is somewhat different.

“pain of training”, as you call it and quite accurate, is a problem.

“too complicated”, is a problem.

My solution has none of the above, but I have yet to investigate if it is possible.

I will stick with my original design with the inclusion of a button. The button with perform the aforementioned function on each row. If the user has not had the skill to limit their entries to the format of “+1” the function will indicate this to the user.

I am hoping to configure the document so that when it is printed to pdf the button will be omitted.
A simple one page writer document, with no “pain of training”, and no complexity.
Cannot get any more perfect than this, ok the next best thing to having the function execute automatically.

#18

One question out of curiosity.
Calc is much better suited for this task than Writer and Calc is easier.
Why not use Calc?

#19

Simple answer is less clutter.

Spreadsheets are great for internal administration.

When it comes to forms filled in and submitted, writer has much less clutter for the user.

The user is presented with a limited number of boxes to fill in and then clicks a button, simple, no pain, no complexity, the forms can be persistent and editable. It has a massive value above all else.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.