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 changed
event ( via the menu Sheet : 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/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

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 Security
must 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.)