We will be migrating from Ask to Discourse on the first week of August, read the details here
Hello @letharion,
To parse an input string like “99G” from a sheet cell, and convert it into the corresponding numerical value, you could use the following method:
Content changed event ( via the menu Sheet : Sheet Events... );code:
Sub on_Content_Changed( oCell As Object )
REM Assign this macro to the "Content Changed" event in the menu "Sheet : Sheet Events...".
REM Adjust below: Specific cells/rows/columns that should be affected by this macro:
Dim aRangeAddress As Object : aRangeAddress = oCell.getRangeAddress()
If aRangeAddress.StartColumn = 0 Then REM Cell must be located on a particular Column ( 0 = Column A ).
' If aRangeAddress.StartRow = 0 Then REM Cell must be located on a particular Row ( 0 = Row 1 ).'
If oCell.getType() = com.sun.star.table.CellContentType.TEXT Then REM Process only text values:
oCell.setValue( SuffixToValue( oCell.String ) )
End If
' End If 'Row
End If 'Column'
End Sub
Function SuffixToValue( strValueSuffix As String ) As Double
REM Converts a formatted value string ( like "1.04M" ) into a Double value.
REM See: https://ask.libreoffice.org/en/question/146487/is-there-a-way-to-enter-numbers-with-suffixes/
REM <strValueSuffix> : Value String to be converted; the last character can be one of the suffices predefined below.
Dim suffices_Big() : suffices_Big = Array("k", "M", "G", "T", "P", "E") REM kilo, Mega, Giga, Tera, Peta, Exa.
Dim suffices_Small() : suffices_Small = Array("m", "µ", "n", "p", "f", "a") REM milli, micro, nano, pico, femto, atto.
Dim dValue As Double : dValue = Val( strValueSuffix )
Dim strSuffix As String : strSuffix = Right( strValueSuffix, 1 )
Dim i As Integer
For i = 0 To uBound( suffices_Big )
If strSuffix = suffices_Big( i ) Then
If strSuffix = strValueSuffix Then dValue = 1
dValue = dValue * 1000 ^ ( i + 1 )
GoTo ReturnValue
End If
Next
For i = 0 To uBound( suffices_Small )
If strSuffix = suffices_Small( i ) Then
If strSuffix = strValueSuffix Then dValue = 1
dValue = dValue / 1000 ^ ( i + 1 )
GoTo ReturnValue
End If
Next
ReturnValue:
SuffixToValue = dValue
End Function
With Regards, lib
If your problem is urgent, the multitude of suffixes limited, and the format for display not bound to preserve the suffixes, you may call a respective Sub as a handler for the sheet-event 'Content changed. You may prepare specific ranges accepting input with suffix by assigning a dedicated named cell style.
The attached example uses the name of the cell style to pass information about the meaning of suffixes to the processing Sub.
To analyse the name of the style is not too complicated. However, assigning a handler to the sheet event Content changed requires to also process complex selections instead of only single cells. The couple of subroutines I wrote for the purpose is not yet flexible to the desirable extent. See yourself, and judge yourself If the usability of the code can override the disadvantage of resorting to custom code.
No guarantee of any kind! Errors expected!
(Edit 1:) Just bumping in the hope to get someone to notice my recent comment on the original question.
The solution presented in the attached example allows for any container for cells that got changed their content. Most of the felt complexity is due to this fact. Restricting the task to single cells changed would simplify the solution, of course. On the other hand it would introduce the risk to get inconsistencies.
If you only care that they are displayed with the units, but not entered as such, then you can define the cell format type as a custom number, 0.0"M" or 0"G". When you enter a number, it will be displayed with the unit but you'll still be able to treat it as a number in formulas.
Any suffix appended as a literal to the 'Numbers' format code will just "be there" without any implied meaning. If it suggests a meaning as standard suffixes for orders of magnitude do, this is extremely misleadimg, and factually a lie.
Using your first example you get shown 1.0M if you entered 1, and the 1 will still be the value of the cell. Thus you are lying by a factor of 1 E6.
This won't solve your problem as stated, but it would avoid the question of mathematical honesty mentioned above by Lupp -- Why not decide on a basic unit for all of your numbers (centimetres, thousands of dollars, milliseconds, kilograms, whatever) and then enter all of your numbers as pure numbers (with that basic unit implied), without the complication of any "suffixes".
The suffixes to numbers the OQ talked of weren't units (or the like) but designators for orders of magnitude which are commonly used as prefixes to SI units, and are standardised for this usage.
The question was about entering numerical values using the unit-prefixes as numer-postfixes for abbreviation. The results are expected to be "pure numbers", I think, and the unit implied otherwise.
This works, at least to cells having a text after a #, like 1gb, 1.1gb, 1.11gb mm, sm, Bytes, or whatever!? No macro's just formulas. It has 100 Rows, but more may be added, is lock, but no password needed to unlock. (/upfiles/15337285156691641.ods)
It's a file I created, I think I posted it earlier with URL to it, http://bit.ly/2Dl8tFK, but today I made it so a hundred rows could be pasted to it to add up #'s with text. And "freeze Cells" so total could be seen. And then pulled it from my download file, this time. (URL now old one). I did not find a demo, I made it, But I did find formula's here, from you @Lupp, from many of your answers! Locked, no password needed. hope that answers you question, is the upfile link OK? They look different
I "edited" my answers! Now my "upfile" is blue. I didn't notice it didn't work. I am doing something wrong, but not sure what...Thanks @Lupp for all yours and others great answers here. Wish I would of found this site years ago. The file is locked, but no password is needed just unlock.
Here's a method that uses built-in functions only, specifically through creative (mis-)use of the CONVERT() function.
For a data value in cell G3, enter:
=IF(LEN(T(G3))=0, G3, CONVERT(VALUE(LEFT(G3, LEN(G3) - 1)), RIGHT(G3, 1) & "m", "m"))
This would return:
Input Value
1 u 1E-06
10 u 1E-05
100 u 1E-04
1 m 0.001
10 m 0.01
100 m 0.1
1 1
10 10
100 100
1 k 1000
10 k 10000
100 k 100000
1 M 1000000
How this works:
if the argument is a numeric value, pass it through;
if the argument is a string, return CONVERT(«numeric part», "«prefix»m", "m"). This is lightly misusing the unit conversion function by going via metres, but it saves having a lookup table.
Quoting @scruss: ", but no-one you know uses them either …"
I do. Who is "you" in the quoted text?
BTW: If you accept long parameter strings, the way I suggested can
-1- also be used with binary prefixes.
-2- also be adapted to work with formulas.
However, to use it without a custom function would require (at least) a standard function TEXTSPLIT(). I do not understand for what reasons such a simple tool is not supplied. Because Excel misses it, probably? It's a mess.
Asked: 2018-02-16 08:19:57 +0200
Seen: 2,645 times
Last updated: Aug 08 '18
Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.
To format them, see https://help.libreoffice.org/Calc/User-defined_Number_Formats. There's no way to enter them without some formulas (or maybe autocorrect could help here?)
The question was upvoted and there were 3 answers, two of them precisely addressing the original question. @librebel and @Lupp (myself) had obviously spent some time to eplain and/or demonstrate their suggested solutions.
Nonetheless there was not a single comment on these solutions, not to speak of a decison which one to accept or to crtisise for some reason.
How shall a contributor stick to his work for such a forum?
Hi @Lupp, it is under investigation, why emails reporting modifications on the threads are not always sent. Thread on Nabble-LibreOffice, maybe the reason about the silence.
I have read the answers, tried "On_Content_Changed", I mainly record macros, call subs, so I could not get the sub to run. Which is ok, I do formulas, and that is what I was hoping for, having an interest to do, and doing, something similar. I now will let others know my limited ability when questioning!!! Cause a lot of these words I never heard of, and just found the Basic modules last month! Mainly learning by reading these answers! And playing with them. Thanks for your help.
(The example I attached to my answer should run "as is" if the excution of the contained code is permitted when loading the file. To get prompted for the permission, the
Macro Securitymust be set toMedium. As the standard functions the OQ was hoping for don't exist, I tried to demonstrate how to approach this problem and lots of similar problems by one kind of means. This was to use the name of a cell style as a surrogate for parameters.)