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

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.

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

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

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

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

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

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

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

Related Articles

• Combine CONCATENATE & TRANSPOSE Functions in Excel
• How to Concatenate If Cell Values Match in Excel
• How to Concatenate with VLOOKUP in Excel
• How to Concatenate Email Addresses in Excel
• How to Concatenate Decimal Places in Excel
• How to Add Parentheses with CONCATENATE Function in Excel