I have bunch of numbers in forms like “3.2M”, “99G” and so on.
I’d like to be able to enter these and have them treated as numbers, not as text, as is the default because of the letter suffixes.
To format them, see User-defined Number Formats - LibreOffice Help. 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 Security must be set to Medium. 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.)
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:
- Copy-paste the code section below into your [My Macros].Standard Basic Library;
- Connect the macro “On_Content_Changed()” to your Sheet’s
Content changedevent ( via the menuSheet : Sheet Events...); - Optionally adjust the macro “On_Content_Changed()”, to limit the number of cells that should respond to this macro.
( Currently it is set only for column A )
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/t/is-there-a-way-to-enter-numbers-with-suffixes/31093
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
Apologies for my lack of reaction. I ended up dropping the project that required this entirely, and I didn’t come back to it until now. This seems to work great, thank you very much. 
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.
Lupp, thanks for clarifying that. I had not understood the intent of the OQ, or the possibility of using SI prefixes as numerical suffixes. Everyone, please ignore my comment above.
Lupp, like ve3oat, I hadn’t interpreted the “units” as a variant of SI. I was thinking “M” for metres, for example. (I’ve never seen anyone use a G suffix for billions. Hence M for millions didn’t click.)
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.
Please note that this won’t work with IEC 60027-2 binary prefixes, but no-one you know uses them either …
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.
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)