Microsoft Excel is a wonderful tool that can help us not only with small spreadsheet stuff but also, with formulas, can automate too many processes that would be daunting otherwise. In this tutorial, we will work with Concatenate with If condition (different conditions) and demonstrate different examples of the process in Excel.

**Table of Contents**hide

## How to Concatenate Cells with If Condition in Excel: 5 Suitable Examples

We can concatenate or combine texts with the **CONCAT**, and **TEXTJOIN** functions and formulas with strings containing the ampersand sign **(&) **between them. While the main idea of using the **IF function** is to have different outputs for two different conditions. So we will combine both of these applications in five different examples below. One of them contains the **IF function** within VBA along with concatenating functionality. For a better understanding, we have divided them into the following subsections.

### 1. Concatenate with Ampersand and If Condition

In the first method, we are gonna use the ampersand (&) to concatenate with the if condition in Excel. Owing to the ampersand sign in any formula, it concatenates the string before and after it together. We will use that to our advantage here. But first, let’s take a look at the dataset.

Here we can see some blank cells in the “middle name” column. And in the fourth column, we intend to concatenate all the parts of the name to form a proper name. In case of the rows with missing middle names, we will concatenate with the first names and last names only. We will use it in our if condition. As a whole, we need to use the **IF** and **ISBLANK** functions here for the purpose.

Follow these steps to see how we can concatenate this dataset with the if condition using the ampersand sign in Excel.

**Steps:**

- First, select cell
**E5**. - Then write down the following formula.

`=B5&IF(ISBLANK(C5)," "," "&C5)&" "&D5`

**🔎 Breakdown of the Formula**

**B5&IF(ISBLANK(C5),” “,” “&C5)&” “&D5**

👉 **ISBLANK(C5) **checks whether cell **C5 **is blank or not and returns a boolean value.

👉 **IF(ISBLANK(C5),” “,” “&C5) **first checks for the output of the previous function. If the output is **TRUE**, then it returns a space as a string. Otherwise, it returns a string concatenated with the value in cell **C5**.

👉 Finally, **B5&IF(ISBLANK(C5),” “,” “&C5)&” “&D5 **concatenates the value of cell **B5**, the output of the previous function, a string containing a space and value of cell **D5**.

- After that, press
**Enter**.

- Now select the cell again. Finally, click and drag the fill handle icon to the end of the column to replicate the formula.

This is how we can concatenate names or cell values with the if condition easily in Excel with the help of the ampersand sign.

### 2. Concatenate with CONCAT Function and If Condition

Next, we are going to concatenate the same dataset with the same if condition in Excel. But this time, we are going to use the** CONCAT function** to concatenate the strings. Besides, we need the use of **IF** and **ISBLANK** functions to complete the formula.

Follow these steps to see how we can do that.

**Steps:**

- First, select cell
**E5**. - Then write down the following formula.

`=CONCAT(B5," ",IF(ISBLANK(C5),"",C5),D5)`

**🔎 Breakdown of the Formula**

**CONCAT(B5,” “,IF(ISBLANK(C5),””,C5),D5)**

👉 **ISBLANK(C5) **checks whether cell **C5 **is blank or not and returns a boolean value.

👉 **IF(ISBLANK(C5),””,C5) **first checks for the output of the previous function. If the output is **TRUE**, then it returns an empty string. Otherwise, it returns the value of cell **C5**.

👉 Finally, **CONCAT(B5,” “,IF(ISBLANK(C5),””,C5),D5) **concatenates the value of cell **B5**, an empty string, the output of the previous function, and the value of cell **D5**.

- Now press
**Enter**.

- Finally, select the cell again and click and drag the fill handle icon to the end of the column to replicate the formula.

Thus, we can concatenate using the **CONCAT function** with the if condition in Excel and perform conditional concatenations.

### 3. Concatenate Cells with TEXTJOIN Function and If Condition

Now we will take the dataset for a change. This time, we are using the following dataset.

Here, we want to put all the players playing in the same team to concatenate them into one cell. For example, players from the team “Chicago Bulls”. But, to concatenate, we are going to use the TEXTJOIN function this time. Additionally, we are going to need the IF function for the if condition, which we will use for the teams.

Follow these steps to see how we can do that in detail and breakdown down the formula.

**Steps:**

- First, select cell
**E5**. - Now insert the following formula in the cell.

`=TEXTJOIN(", ",TRUE,IF($C$5:$C$13="Chicago Bulls",B5:B13,""))`

**🔎 Breakdown of the Formula**

**TEXTJOIN(“, “,TRUE,IF($C$5:$C$13=”Chicago Bulls”,B5:B13,””))**

👉 First, **IF($C$5:$C$13=”Chicago Bulls”,B5:B13,””) **function checks where the values in the range **C5:C13 **matches with “Chicago Bulls”. It will generally be an array of booleans containing **TRUE**s and **FALSE**s. Depending on this array, the function will return the values from the range **B5:B13**. It will only return the values from those positions where the array contains **TRUE**. Otherwise, it will have an empty string in place.

👉 Finally, **TEXTJOIN(“, “,TRUE,IF($C$5:$C$13=”Chicago Bulls”,B5:B13,””)) **joins all of the components of the array with a delimiter of a comma and a space ignoring all the empty values.

- Finally, press
**Enter**.

As a result, we can concatenate with the help of the **TEXTJOIN **function along with the if condition in Excel.

**Read More: **How to Concatenate Different Fonts in Excel

### 4. Concatenate Cells If Same Value Exists in Another Column

Now let’s try a different type of concatenation this time with the if condition in Excel. Let’s take the dataset like this.

This time, we are going to keep concatenating all the cell values from column **C** with the previous one until the adjacent values from column **B **match. Otherwise, it will reset. We are gonna need to use the** IF function** of Excel for the if condition and the ampersand symbol for the concatenation.

Follow these steps to see how we can do that.

**Steps:**

- First of all, select cell
**D5**. - Second, write down the following formula.

`=IF(B5<>B4,C5,D4&", "&C5)`

- Third, press
**Enter**.

- Finally, select the cell again and click and drag the fill handle icon to the end of the column.

This way we can perform this unique concatenation with the if condition in Excel.

### 5. Concatenate Multiple Cells Based on User-Defined Function

Again, we can ignore all of those functions to create a formula and define our own one to concatenate including the if condition in Excel. But we are gonna need the help of VBA for that.

Microsoft’s Visual Basic for Applications (VBA) is an event-driven language that provides us with functionalities ranging from entering a cell value to automating boring and repetitive processes in large datasets. But this time, we are going to use the feature to create a custom function. The code will contain the if condition within it.

Before using the codes or any kind of VBA feature, you need to have the **Developer **tab on your ribbon. By default, it is not present there. So, you have to display the Developer tab on your ribbon.

Once you have the tab, you can follow these steps to create the custom function to concatenate along with the if condition in Excel easily.

**Steps:**

- First, go to the
**Developer**tab on your ribbon. - Then select
**Visual Basic**from the**Code**group section.

- As a result, the VBA window will pop up.
- Now click on the
**Insert**tab on it. - After that, select
**Module**from the drop-down list.

- If the module isn’t selected already, select it now.
- Then insert the following code in the module.

```
Function CONCATIF(Criteria As Range, Concatcriteria As Variant, ConcatRange As Range, Optional Delimiter As String = ",") As Variant
Dim Results As String
On Error Resume Next
If Criteria.Count <> ConcatRange.Count Then
CONCATIF = CVErr(xlErrRef)
Exit Function
End If
For j = 1 To Criteria.Count
If Criteria.Cells(j).Value = Concatcriteria Then
Results = Results & Delimiter & ConcatRange.Cells(j).Value
End If
Next j
If Results <> "" Then
Results = VBA.Mid(Results, VBA.Len(Delimiter) + 1)
End If
CONCATIF = Results
Exit Function
End Function
```

- Now close the VBA window and go back to the spreadsheet and select cell
**F5**. - Then write down the following formula.

`=CONCATIF($B$5:$B$13,E5,$C$5:$C$13,", ")`

- After that, press
**Enter**.

- Finally, select the cell again and click and drag the fill handle icon to replicate the formula for the rest of the cells.

Hence, we can use VBA to define custom functions to perform the concatenation with the if condition in Excel.

**Read More: **Excel INDEX MATCH to Concatenate Multiple Results

## How to Concatenate If Cell Is Not Blank in Excel

Let’s take another example with the condition to concatenate if the cell beside that is not blank in Excel. This would be the dataset for that.

We can do that with a formula combining **CONCAT**, **IF****, **and **ISBLANK** functions. Follow these steps for more details.

**Steps:**

- First, select cell
**E5**. - Then write down the formula in it.

`=CONCAT(IF(ISBLANK(C5:C12),"",B5:B12))`

**🔎 Breakdown of the Formula**

**CONCAT(IF(ISBLANK(C5:C12),””,B5:B12))**

👉 First, **ISBLANK(C5:C12) **checks if there is any blank value in the range **C5:C12**. It returns an array with **TRUE **values where there was a blank in the range and **FALSE **values otherwise.

👉 **IF(ISBLANK(C5:C12),””,B5:B12) **also returns an array replacing an empty string for the **TRUE **values and the corresponding value from the range **B5:B12 **if the value from the previous function was **FALSE**.

👉 Finally, **CONCAT() **concatenates all the components in the array and returns the final result.

- Finally, press
**Enter**.

This is how we can conditionally concatenate cells if the adjacent cell is not blank in Excel.

**Read More: **How to Concatenate Arrays in Excel

## How to Concatenate If Cells Match Multiple Criteria in Excel

Let’s take one more example before we wrap this up. Here is the dataset.

Here we are going to concatenate the “Name” column with the “Score” column, but the condition is if they are from class 11 and team A only – so multiple criteria one. We are gonna need to use the **IF** and **CONCAT** functions to develop the formula for this one.

Follow these steps to see how we can concatenate with the if condition for multiple conditions in Excel.

**Steps:**

- First, select cell
**F5**. - Then write down the following formula in it.

`=IF(C5=11,IF(D5="A",CONCAT(B5,": ",E5),""),"")`

**🔎 Breakdown of the Formula**

**IF(C5=11,IF(D5=”A”,CONCAT(B5,”: “,E5),””),””)**

👉 Initially, **IF(C5=11,IF(…),””) **function checks if the cell value of **C5 **is 11 or not. If it is, then the formula proceeds to the next **IF **function. Otherwise, it returns an empty string.

👉 **CONCAT(B5,”: “,E5) **concatenates the value of cell **B5**, string “: “, and the value of cell **E5**.

👉 **IF(D5=”A”,CONCAT(B5,”: “,E5),””) **checks if the value of cell **D5 **is “A” or not. If the value is “A”, the function returns the concatenated result. Otherwise, it returns an empty string.

👉 Finally, a combination with both the **IF **functions in a looped manner returns the concatenated result only for those from class 11 and team A.

- Now press
**Enter**.

- After that, select the cell again. Finally, click and drag the fill handle icon to the end of the column to replicate the formula for those cells.

Thus we can concatenate along with multiple if conditions in Excel.

## 💬 Things to Remember

- The
**CONCATENATE function**is an earlier version of the**CONCAT**But, both functions give the same result. - The
**TEXTJOIN**function may only be available in the newer versions of Microsoft Excel.

**Download Practice Workbook**

You can download the workbook used for the demonstration from the link below.

## Conclusion

So this was all about concatenation and how to concatenate with the if condition in Excel. Hopefully, you have grasped the idea and can apply it to custom datasets. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.