Ask Your Question

Is there a way to enter numbers with suffixes?

asked 2018-02-16 08:19:57 +0100

letharion gravatar image

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 flag offensive close merge delete


To format them, see There's no way to enter them without some formulas (or maybe autocorrect could help here?)

Mike Kaganski gravatar imageMike Kaganski ( 2018-02-16 08:28:13 +0100 )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?

Lupp gravatar imageLupp ( 2018-03-05 11:21:05 +0100 )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.

m.a.riosv gravatar imagem.a.riosv ( 2018-03-05 13:16:28 +0100 )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.

Mark McLean gravatar imageMark McLean ( 2018-03-08 16:43:29 +0100 )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.)

Lupp gravatar imageLupp ( 2018-03-08 17:17:00 +0100 )edit

5 Answers

Sort by » oldest newest most voted

answered 2018-02-17 04:23:54 +0100

librebel gravatar image

updated 2018-02-17 04:25:33 +0100

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 )


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() = 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:
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
    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
    SuffixToValue = dValue
End Function

With Regards, lib

image description

edit flag offensive delete link 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. :)

letharion gravatar imageletharion ( 2018-05-11 18:56:57 +0100 )edit

answered 2018-02-16 14:43:51 +0100

Lupp gravatar image

updated 2018-03-05 11:24:07 +0100

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.

edit flag offensive delete link 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.

Lupp gravatar imageLupp ( 2018-03-08 17:25:04 +0100 )edit

answered 2018-02-16 11:54:14 +0100

Paul451 gravatar image

updated 2018-02-16 11:55:31 +0100

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.

edit flag offensive delete link more



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.

Lupp gravatar imageLupp ( 2018-02-16 14:08:31 +0100 )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".

ve3oat gravatar imageve3oat ( 2018-02-16 17:09:23 +0100 )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.

Lupp gravatar imageLupp ( 2018-02-16 17:39:00 +0100 )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.

ve3oat gravatar imageve3oat ( 2018-02-16 20:02:43 +0100 )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.)

Paul451 gravatar imagePaul451 ( 2018-02-17 04:43:21 +0100 )edit

answered 2018-08-08 12:28:56 +0100

updated 2018-08-08 18:26:45 +0100

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)

edit flag offensive delete link 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

Lupp gravatar imageLupp ( 2018-08-08 13:07:56 +0100 )edit

It's a file I created, I think I posted it earlier with URL to it,, 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

Mark McLean gravatar imageMark McLean ( 2018-08-08 14:02:23 +0100 )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.

Mark McLean gravatar imageMark McLean ( 2018-08-08 14:07:35 +0100 )edit

answered 2018-08-06 01:35:02 +0100

scruss gravatar image

updated 2018-08-08 03:52:59 +0100

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.

edit flag offensive delete link more


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

scruss gravatar imagescruss ( 2018-08-06 01:37:00 +0100 )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.

Lupp gravatar imageLupp ( 2018-08-06 11:12:18 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-02-16 08:19:57 +0100

Seen: 1,986 times

Last updated: Aug 08 '18