IMAGINARY Function in Excel

The IMAGINARY function in Excel gives you the imaginary part of a complex number. So for 5+3i, it returns 3, the number in front of the i.

Excel stores complex numbers as text, so you can’t do regular math on them. IMAGINARY pulls out the imaginary part as a normal number, which you can then use with other functions, like the FILTER or IF functions.

In this article, I’ll show you how to use IMAGINARY with five examples, including how to get the imaginary parts of a whole column with one formula (it needs a small trick) and how to fix the #NUM! error.

IMAGINARY Function Syntax

Here is the syntax of the IMAGINARY function:

=IMAGINARY(inumber)

• inumber – The complex number whose imaginary part you want. It can be a complex number written as text, such as “5+3i” or “6-j”, or a reference to a cell that holds one.

When you type the complex number straight into the formula, put it in double quotes. For example, =IMAGINARY("6-2i") returns -2.

The letter at the end has to be a lowercase i or j. IMAGINARY reads both, but an uppercase I or J gives you the #NUM! error.

If inumber is just a regular number, there’s no imaginary part, so IMAGINARY returns 0. A blank cell also returns 0 instead of an error.

The IMAGINARY function works in Excel for Microsoft 365, Excel 2024, 2021, 2019, 2016, and Excel for Mac. The single formulas that fill a whole column need Excel 365 or Excel 2021 and later.

When to Use the IMAGINARY Function

Use this function when you need to:

• Get the imaginary part of complex numbers stored as text
• Split impedance values into resistance and reactance
• Filter a list based on the imaginary part of each value
• Check whether a complex number calculation returned a real number
• Find and fix complex numbers that were typed in a form Excel doesn’t recognize

Let me show you a few practical examples of how to use the IMAGINARY function.

Example 1: Get the Imaginary Part of a Complex Number

Let’s start with a simple example.

Below is a list of complex numbers written in a few different ways, and I want to get the imaginary part of each one in column B.

Here is the formula to enter in cell B2 and copy down:

=IMAGINARY(A2)

IMAGINARY returns the number in front of the i (or j), along with its sign. So 5+3i gives 3, and 2-7i gives -7.

When there’s no number in front of the letter, the imaginary part is 1. That’s why -4+i returns 1, while 6-j and -i both return -1.

The j in 6-j and 1.5+2.25j works the same way as i. And 8i has no real part at all, so its imaginary part is simply 8.

The value in A8 is just 12, a real number with no imaginary part, so IMAGINARY returns 0.

I’ve kept this first example as a per-row formula so you can see what IMAGINARY does with each value. The next example handles a whole column with one formula.

Example 2: Split Complex Numbers into Real and Imaginary Parts

In Excel 365 (and Excel 2021 or later), you can get the imaginary parts of a whole column with a single formula, but IMAGINARY needs a small trick to do it.

In AC circuits, impedance is a complex number. Its real part is the resistance, and its imaginary part is the reactance, both measured in ohms.

Below are the impedance readings of a speaker at different frequencies, and I want the resistance in column C and the reactance in column D.

Here is the formula to enter in cell D2:

=IMAGINARY(+B2:B10)

This spills the reactance for all nine frequencies down to D10. At 20 Hz, the impedance is 7.2+3.1j, so the reactance is 3.1 ohms.

The plus sign in front of the range is the important part. Without it, IMAGINARY returns a single #VALUE! error instead of nine results.

Here’s what that looks like. I’ve entered the same formula without the plus sign in cell F2:

=IMAGINARY(B2:B10)

This happens because IMAGINARY can’t take a multi-cell range reference like B2:B10. It only works with single values, or with a list of values held in memory (called an array).

Putting a plus sign in front of the range makes Excel hand IMAGINARY its values as an array, so it returns one result for each row.

Now for the resistance. The IMREAL function returns the real part of a complex number, and it needs the same plus sign.

Here is the formula to enter in cell C2:

=IMREAL(+B2:B10)

Now each impedance is split into its two parts. At 80 Hz, the impedance is just 32.5 with no j part, so the resistance is 32.5 and the reactance is 0.

If you are using Excel 2019 or earlier, enter the below formula in cell D2 and then copy it down the range:

=IMAGINARY(B2)

And for the resistance, enter the below formula in cell C2 and then copy it down the range:

=IMREAL(B2)

Example 3: Filter a List by the Imaginary Part

Since IMAGINARY returns a regular number, you can use it inside other functions. Here, it decides which rows the FILTER function keeps (FILTER is available in Excel 365 and Excel 2021 or later).

Below is a list of equipment in a workshop with the impedance of each one, and I want to list only the inductive loads starting in column D.

An inductive load, like a motor, has a positive reactance, so the imaginary part of its impedance is greater than 0. A negative reactance means the load is capacitive, and a reactance of 0 means it’s purely resistive, like a heater.

Here is the formula to enter in cell D2:

=FILTER(A2:B9,IMAGINARY(+B2:B9)>0)

How this formula works:

• IMAGINARY(+B2:B9) returns the reactance of each item: 4.8, 0, 5.4, -9, 0, 6, -40, and 14.4.
• >0 checks each of those values and returns TRUE only for the positive ones.
• FILTER returns the rows of A2:B9 where the check is TRUE, with both the equipment name and its impedance.

The result is the four motor-driven tools: the Table Saw, Air Compressor, Dust Collector, and Bench Grinder. The LED Shop Lights and the Capacitor Bank are left out because their reactance is negative.

The plus sign matters here too. With a plain B2:B9 inside IMAGINARY, the whole formula returns #VALUE!.

To list the capacitive loads instead, change >0 to <0.

Example 4: Check If a Complex Number Is Actually a Real Number

Here’s one that can trip you up.

Below are some complex numbers raised to different powers with the IMPOWER function, and I want to get the imaginary part of each result in column D and check which results are real numbers in column E.

You probably know that i² is -1, so the first result should be just -1. But cell C2 shows -1+1.22514845490862E-16i.

That’s because Excel works out powers of complex numbers using angles, and the result carries a tiny rounding error. So a part that should be exactly 0 comes out as a number very close to 0.

Here is the formula to get the imaginary parts, to enter in cell D2:

=IMAGINARY(+C2:C9)

For i², IMAGINARY returns 1.22515E-16, which is 0.000000000000000122515. It’s practically 0, but it isn’t 0, and the same thing happens with i⁴, (1+i)⁴, and (2+2i)⁴.

Important: Don’t check for a real number by comparing IMAGINARY with 0 directly. For i², =IMAGINARY(C2)=0 returns FALSE, even though i² is -1.

To fix this, round the imaginary part before comparing it. Here is the formula to enter in cell E2:

=IF(ROUND(IMAGINARY(+C2:C9),9)=0,"Yes","No")

The ROUND function rounds each imaginary part to 9 decimal places. That turns the tiny leftovers into 0 but keeps genuine imaginary parts like 2 (from (1+i)²) or -1 (from i³) as they are.

So i², i⁴, (1+i)⁴, and (2+2i)⁴ show Yes, while results like 3-4i and 8+6i show No.

If you are using Excel 2019 or earlier, enter the below formula in cell E2 and then copy it down the range:

=IF(ROUND(IMAGINARY(C2),9)=0,"Yes","No")

Example 5: Fix the #NUM! Error in IMAGINARY

Let’s finish with the error you’re most likely to run into.

Below are answers that students typed into a shared practice sheet, and I want to get the imaginary part of each answer in column C.

Here is the formula to enter in cell C2:

=IMAGINARY(+B2:B9)

Five of the eight answers return the #NUM! error. IMAGINARY gives this error when the text isn’t written in a form Excel recognizes as a complex number.

Hannah, Stuart, and Robert used an uppercase I or J, and Excel only accepts a lowercase i or j.

Diana and Spencer put a space between the sign and the i (4 – i and 8 + i). When there’s no number in front of the i, Excel needs the sign right next to it.

Spaces next to a number are fine, though. That’s why Angela’s 6+ 2i and Valerie’s 9 – 8j work.

To fix all of these in one go, clean the text with the LOWER and SUBSTITUTE functions before IMAGINARY reads it. Here is the formula to enter in cell D2:

=IMAGINARY(SUBSTITUTE(LOWER(B2:B9)," ",""))

How this formula works:

• LOWER(B2:B9) converts every answer to lowercase, so 7+2I becomes 7+2i.
• SUBSTITUTE(...," ","") removes all the spaces, so 4 – i becomes 4-i.
• IMAGINARY then reads the cleaned text, and all eight answers return their imaginary part.

This formula doesn’t need a plus sign, because the LOWER and SUBSTITUTE functions already hand IMAGINARY an array.

If complex numbers copied from a web page still return #NUM! after this, they may contain non-breaking spaces or a special minus sign. Those characters look the same but aren’t the ones this formula replaces.

To clean those too, wrap the SUBSTITUTE(LOWER(B2:B9)," ","") part of the formula in one more SUBSTITUTE for each character. Replace CHAR(160) with “” to remove non-breaking spaces, and UNICHAR(8722) with “-” to swap the special minus sign for a normal one.

If you are using Excel 2019 or earlier, enter the below formula in cell D2 and then copy it down the range:

=IMAGINARY(SUBSTITUTE(LOWER(B2)," ",""))

So these are some practical ways to use the IMAGINARY function in Excel to get the imaginary part of a complex number.

Just remember to put a plus sign in front of your range when you want one formula for a whole column, and round the result before you compare it with 0. I hope you found this article helpful.

List of All Excel Functions

Other Excel Articles You May Also Like:

• IMSUB Function in Excel
• SUBSTITUTE Function in Excel
• LOWER Function in Excel
• IMABS in Excel
• IMARGUMENT guide
• IMCONJUGATE function
• COMPLEX Function in Excel
• IMDIV