How can I format complex numbers in excel?

user_10338103 0
Viewed 116

I create complex numbers in Excel for example with =COMPLEX(ROUND(A10;3);ROUND(B10;3)). However, if either the real or imaginary part is 0, it gets dropped, like 0.500 instead of 0.500 + 0.000i or 0.800i instead of 0.000 + 0.800i. It looks awful in tables. Using FIXED instead of ROUND gives the same result. How can I get this formatted properly?

Thanks in advance Engelbert

complex-numbers formatting excel
1 Answers

Based on the answer I linked in my comment above, here is an example of a UDF that will format complex numbers and include the zero value. The bonus in this function is that it will handle both a Range input (as a single cell) or a numerical value.

Option Explicit

Public Function FormatComplex(r As Variant, _
                              Optional i As Variant, _
                              Optional fmt As String = "0.000") As String
    '--- returns a formatting string depicting the complex number
    '    represented by r and i. these parameters may be given as
    '    a (single cell) range, or a value
    '    INPUTS: r can be a (single-cell) Range or a value represented
    '                  as a string, double, integer, or complex value
    '            i can be a (single-cell) Range or a value represented
    '                  as a string, double, integer, or complex value
    '                  (should be omitted if "r" is a Complex value)
    '            fmt is a VBA style format string (cannot be used if
    '                  the "r" parameter is Complex)
    Dim realPart As Double
    Dim imgPart As Double
    If TypeName(r) = "Range" Then
        '--- must be a single cell
        If r.Count > 1 Then
            FormatComplex = CVErr(xlErrRef)
            Exit Function
        End If
        If Right$(r, 1) = "i" Then
            '--- the value given is already assumed to be complex, so
            '    split up the parts here
            realPart = Application.WorksheetFunction.ImReal(r)
            imgPart = Application.WorksheetFunction.Imaginary(r)
        Else
            realPart = r.Value
        End If
    Else
        realPart = r
    End If
    If Not IsMissing(i) Then
        If TypeName(i) = "Range" Then
            '--- must be a single cell
            If i.Count > 1 Then
                FormatComplex = CVErr(xlErrRef)
                Exit Function
            End If
            imgPart = i.Value
        Else
            imgPart = i
        End If
    End If
    
    Dim result As String
    result = Format(realPart, fmt)
    If Left$(fmt, 1) <> "+" Then
        '--- if the user-specified format string does not explicitly
        '    call out a sign, then we have to add it ourselves
        '    this might be desirable if the user does not want a
        '    leading "+" in front of the real part
        If imgPart >= 0 Then
            result = result & "+"
        Else
            '--- assume the "-" is included in the number formatting
            '    so don't add it here
        End If
    End If
    result = result & Format(imgPart, fmt) & "i"
    FormatComplex = result
End Function
user_47177550
Related