We will be migrating from Ask to Discourse on the first week of August, read the details here

# Is there a way to enter numbers with suffixes?

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.

edit retag close merge delete

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?)

( 2018-02-16 08:28:13 +0200 )edit

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?

( 2018-03-05 11:21:05 +0200 )edit

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.

( 2018-03-05 13:16:28 +0200 )edit

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.

( 2018-03-08 16:43:29 +0200 )edit

(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.)

( 2018-03-08 17:17:00 +0200 )edit

Sort by » oldest newest most voted

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:

1. Copy-paste the code section below into your [My Macros].Standard Basic Library;
2. Connect the macro “On_Content_Changed()” to your Sheet’s Content changed event ( via the menu Sheet : Sheet Events... );
3. 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:

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 <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

more

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. :)

( 2018-05-11 18:56:57 +0200 )edit

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.

more

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.

( 2018-03-08 17:25:04 +0200 )edit

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.

more

1

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.

( 2018-02-16 14:08:31 +0200 )edit

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".

( 2018-02-16 17:09:23 +0200 )edit

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.

( 2018-02-16 17:39:00 +0200 )edit

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.

( 2018-02-16 20:02:43 +0200 )edit

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

( 2018-02-17 04:43:21 +0200 )edit

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)

more

Would you mind to post a link to the thread where you found the demo document?
The plain URL of the upfile you linked in is https://ask.libreoffice.org/upfiles/15337241174184731.ods

( 2018-08-08 13:07:56 +0200 )edit

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

( 2018-08-08 14:02:23 +0200 )edit

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.

( 2018-08-08 14:07:35 +0200 )edit

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:

1. if the argument is a numeric value, pass it through;

2. 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.

more

Please note that this won't work with IEC 60027-2 binary prefixes, but no-one you know uses them either …

( 2018-08-06 01:37:00 +0200 )edit

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.

( 2018-08-06 11:12:18 +0200 )edit