# How to Concatenate Cells with If Condition in Excel (5 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

## 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 TRUEs and FALSEs. 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.

### 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.

## 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.

## 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.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Abrar-ur-Rahman Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  