Excel VBA functions for formatting numbers using SI prefixes (and back)

Excel does not have any built-in way of formatting numbers such that the numbers are displayed in a neatly readable way using SI prefixes, like m for milli (10^-3), k for kilo (10^3), µ for micro (10^-6) etc. Here I provide a VBA function I wrote to fill this need. In addition, I added support for using the prefix notation of Spice circuit simulators where both m and M are interpreted as milli and Meg has to be used to signify mega. Also, u has to be used instead of µ to indicate micro in Spice.

As icing on the cake, I also wrote a function to convert the resulting strings back to numbers.

The functions are called FormatPrefix and ValPrefix. Source code with detailed descriptions of the input arguments are shown further down in this post.

Below is a link to an Excel document with the functions built into a VBA module and a worksheet with an example table of values fed to the function to display its output for a large variation of input arguments:

[Excel] ExcelPrefixFormattingFunction.xls

Here is a screenshot demonstrating the formatting:

Excel table showing the output of FormatPrefix for a large set of input arguments.
Excel table showing the output of FormatPrefix for a large set of input arguments.

The source code is shown below:

Public Function FormatPrefix(value As Double, _
                             Optional precision As Integer = 3, _
                             Optional blankZero As Boolean = False, _
                             Optional space As Boolean = False, _
                             Optional spice As Boolean = False) As String
    ' Convert a number to a string, usually with an SI prefix at the end.
    '
    ' Arguments:
    ' value is the value to be converted.
    ' Optional precision argument determines number of significant digits to be printed (default 3).
    ' Optional blankZero argument determines if zero should return the empty string (default false).
    ' Optional space argument determines if a space should be inserted after the number but before
    '  a possible prefix (default false).
    ' Optional spice argument determines if Spice prefixes should be used instead of metric ones
    '  (default false). The only differences are Meg vs M and u vs µ.
    ' Values above or equal to 1e15 or below 1e-18 are printed using scientific notation without
    ' prefix.
    ' The decimal indicator is always a point, even if the locale says it should be a comma.
    '
    ' Written by Per Magnusson, Axotron, 2016-02-26
    ' This code is public domain and comes without any warranty. Enjoy!
    
    ' Handle the value 0
    If value = 0 Then
        If blankZero Then
            FormatPrefix = ""
        Else
            If space Then
                FormatPrefix = "0 "
            Else
                FormatPrefix = "0"
            End If
        End If
        Exit Function
    End If
    
    ' Handle negative numbers
    sign = 1
    If value < 0 Then         sign = -1 ' store the sign         value = -value ' make value positive until later     End If          ' Calculate exponent and mantissa     exponent = Int(Log(value) / Log(10#))     mantissa = Round(value / (10 ^ exponent), precision - 1)     If mantissa >= 10 Then
        ' Handle the case where the mantissa is rounded up to 10
        exponent = exponent + 1
        mantissa = mantissa / 10
    End If
    exponent3 = 3 * Int(exponent / 3) 'exponent rounded down to nearest multiple of 3
    
    ' Find the right prefix
    If exponent >= 15 Then
        ' Too big to represent with the supported prefixes, use scientific notation
        prefix = "sci"
    ElseIf exponent >= 12 Then
        prefix = "T"
    ElseIf exponent >= 9 Then
        prefix = "G"
    ElseIf exponent >= 6 Then
        If spice Then
            prefix = "Meg"
        Else
            prefix = "M"
        End If
    ElseIf exponent >= 3 Then
        prefix = "k"
    ElseIf exponent >= 0 Then
        prefix = ""
    ElseIf exponent >= -3 Then
        prefix = "m"
    ElseIf exponent >= -6 Then
        If spice Then
            prefix = "u"
        Else
            prefix = "µ"
        End If
    ElseIf exponent >= -9 Then
        prefix = "n"
    ElseIf exponent >= -12 Then
        prefix = "p"
    ElseIf exponent >= -15 Then
        prefix = "f"
    ElseIf exponent >= -18 Then
        prefix = "a"
    Else
        ' Too small to represent with the supported prefixes, use scientific notation
        prefix = "sci"
    End If
        
    If prefix = "sci" Then
        ' Use scientific notation
        formatStr = "0"
        If precision > 1 Then
            formatStr = formatStr & "."
            For ii = 1 To precision - 1
                formatStr = formatStr & "0"
            Next
        End If
        formatStr = formatStr & "e-0"
        prefix = ""
        value = sign * mantissa * 10 ^ exponent
    Else
        decimals = precision - (exponent - exponent3) - 1
        If decimals > 0 Then
            formatStr = "0."
            For ii = 1 To decimals
                formatStr = formatStr & "0"
            Next
        Else
            formatStr = "0"
        End If
        value = sign * mantissa * 10 ^ (exponent - exponent3)
    End If
    optSpace = ""
    If space Then
        optSpace = " "
    End If
    FormatPrefix = Replace(Format(value, formatStr) & optSpace & prefix, ",", ".")
    'FormatPrefix = formatStr & optSpace & prefix
End Function



Public Function ValPrefix(str As String) As Double
    ' Convert a number formatted as a string using FormatPrefix() back to a number.
    '
    ' Arguments:
    ' str is the string to be converted
    ' Error checking is very limited.
    '
    ' Written by Per Magnusson, Axotron, 2016-02-26
    ' This code is public domain and comes without any warranty. Enjoy!
    
    exponent = 0
    prefix = ""
    strlen = Len(str)
    
    If StrComp(Right(str, 1), "A") >= 0 Then
        ' There is a letter at the end of the value
        ii = 1
        While StrComp(Mid(str, strlen - ii, 1), "A") >= 0
            ii = ii + 1
        Wend
        prefix = Mid(str, strlen - ii + 1)
        Select Case prefix
            Case "T"
                exponent = 12
            Case "G"
                exponent = 9
            Case "M"
                exponent = 6
            Case "Meg"
                exponent = 6
            Case "k"
                exponent = 3
            Case "m"
                exponent = -3
            Case "u"
                exponent = -6
            Case "µ"
                exponent = -6
            Case "n"
                exponent = -9
            Case "p"
                exponent = -12
            Case "f"
                exponent = -15
            Case "a"
                exponent = -18
            Case Else
                ' Error, handle it somehow?
                ValPrefix = "Prefix error"
                Exit Function
        End Select
    End If
    ValPrefix = Val(str) * 10 ^ exponent
    
End Function

4 thoughts on “Excel VBA functions for formatting numbers using SI prefixes (and back)

  1. Hi,
    The ExcelPrefixFormattingFunction.xls is not available.
    Can you verify please.
    Thank you.

  2. Hi Stéphane,

    Thanks for pointing that out. I have corrected the link, so it should work now.

    Per

Leave a Reply to Per Magnusson Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.