You have come to the right place if you are looking for the answer or some unique tips to create a complex formula in Excel. This article will walk you through each and every step with appropriate examples. As a result, you can use them easily for your purpose. Let’s move on to the article’s main discussion.

**Table of Contents**Expand

## What Is a Complex Formula in Excel?

In the complex formula, there is the usage of more than one Excel function or mathematical operator. And, in a simple formula, one single function or operator is used. Proper usage of parentheses and planning is needed to create a complex formula else it may return wrong output.

## The Order of Operations in Formula of Excel – PEMDAS

Excel also follows the mathematical order of operations which is known as the **PEMDAS **rule. The description of the **PEMDAS **rule is as follows:

**P = Parentheses**: First, you will do the calculation of the operations enclosed in the parentheses

**E = Exponential**: Then, calculate the power and inverse power calculations.

**MD = Multiplication and Division**: After that, complete the calculations related to the multiplications and divisions whatever comes at first.

**AS = Addition and Subtraction**: At last, you will do the addition and subtraction calculations in the formula.

## How to Create a Complex Formula in Excel: Step-by-steps

In this section, I will show you the quick and easy steps to create a complex formula in Excel on the Windows operating system. This article contains detailed explanations with clear illustrations for everything. I have used the **Microsoft 365** version here. However, you may use any other version depending on your availability. Please leave a comment if any part of this article does not work in your version.

We will use a formula to extract five-digit numbers from any part of a string in Excel. We’ll use the **CONCAT**, **SEQUENCE**, **MID**, **IFERROR**, and **LEN **functions for the first time in this section. Moreover, we have changed our dataset slightly for this method. First, I am showing you the complex formula, and then I will show you how I have made it. This is the complex formula to extract five digits from any part of a string.

`=CONCAT(IFERROR(0+MID(B5,SEQUENCE(LEN(B5)),1),""))`

__Road-map to Create a Complex Formula in Excel__

First, you must make a roadmap for getting the target output. Our plan is as follows:

- First, we will calculate the length of the string.
- Then, we will insert all digits of the string individually.
- After that, we will add 0 with the digits, so only the numerical digits will give output and others will return an error.
- After excluding the error cells, we have to combine the other cells so we will get the numerical digits of the string.

**📌** Step 1: Insert the LEN Function

- First, calculate the length of the string using
**the LEN function.**For this, insert the following formula in**cell C5**:

`=LEN(B5)`

- The output will be
**11**for the string in cell**B5**.

**📌** Step 2: Use the SEQUENCE Function to Create a Serial

- After that, create a list of serials from 1 to 11 using
**the SEQUENCE function**. For this, insert the following formula in**cell D5**:

`=SEQUENCE(C5)`

- As a result, you will see that a list of numbers from 1 to 11 is created in cell range
**D5:D15**.

**Read More:** How to Create a Formula in Excel for Multiple Cells

**📌** Step 3: Add MID Function to Insert Characters of String in Individual Cells

- Now, I will use
**the MID function**to insert all the characters of String in cell**B5**in individual cells. For this, insert the following formula in cell**E5**:

`=MID(B5,D5#,1)`

- As a result, you will get all the characters of the string in cell
**B5**individually in cell range of**E5:E15**.

**Note**:

The **D5#** means the cell range **D5:D15**. As all the cells of this range are the output of the formula of cell **D5**, the range is converted to **D5#**.

**📌** Step 4: Use IFERROR Function to Remove the Letter Characters

- Now, I will add 0 with the cells of range
**E5:E15**. - As there are some numbers and some letters, only numbers can be added with zero and return output but the letters will return an error.
- Using
**the IFERROR function**, I will eliminate the errors with a blank cell. For this insert the following formula in**cell F5**:

`=IFERROR(0+E5#,"")`

- Consequently, you will get the only number digits in cell
**F5:F15**and the remaining cells are**blank**.

**📌** Final Step: Use CONCAT Function to Combine Cell Values

- Finally, you have to use the
**CONCAT**function to combine cells of range**F5:F14**, so you will get the target output which was the only numerical digits of the string in**cell B5**. - For this, insert the following formula in
**cell G5**:

`=CONCAT(F5#)`

This is how you can create a complex formula in Excel. The main thing is: you have to brainstorm first about your target, then apply them step by step, and finally merge them into a formula. Thus you can create a complex formula.

**Read More:** How to Insert Formula for Entire Column in Excel

You can download the practice workbook from here:

## Conclusion

In this article, you have found how to create a complex formula in Excel. I hope you found this article helpful. Please leave comments, suggestions, or queries if you have any in the comment section below.

**Related Articles**

- How to Apply Formula in Excel for Alternate Rows
- How to Create a Conditional Formula in Excel
- How to Create a Custom Formula in Excel
- How to Create a Formula Using Defined Names in Excel
- How to Create a Formula in Excel without Using a Function

**<< Go Back to How to Create Excel Formulas | Excel Formulas | Learn Excel**