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

Method 1 – Concatenate with Ampersand and IF Condition

We can see some blank cells in the “middle name” column. In the fourth column, we want to concatenate all the parts of the name to form a proper name. For rows with missing middle names, we will concatenate with the first names and last names only.

Steps:

• Select cell E5.
• Insert the following formula.

• Press Enter.

• Click and drag the fill handle icon to the end of the column to replicate the formula.

Method 2 – Concatenate with the CONCAT Function and If Condition

Steps:

• Select cell E5.
• Use the following formula.

• Press Enter.

• Click and drag the fill handle icon from E5 to the end of the column to replicate the formula.

Method 3 – Concatenate Cells with the TEXTJOIN Function

Here, we want to put all the players playing in the Chicago Bulls into one cell.

Steps:

• Select cell E5.
• Insert the following formula in the cell.

• Press Enter.

Read More: How to Concatenate Different Fonts in Excel

Method 4 – Concatenate Cells If the Same Value Exists in Another Column

We are going to keep concatenating all the cell values from column C with the previous one for as long as the adjacent values from column B match. Otherwise, it will reset.

Steps:

• Select cell D5.
• Insert the following formula.

• Press Enter.

• Click and drag the fill handle icon to the end of the column.

Method 5 – Concatenate Multiple Cells with a User-Defined Function

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. Use the following article to display the Developer tab on your ribbon.

Steps:

• Go to the Developer tab on your ribbon.
• Select Visual Basic from the Code group section.

• The VBA window will pop up.
• Click on the Insert tab on it.
• Select Module from the drop-down list.

• If the module isn’t selected already, select it now.
• 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

• Close the VBA window and select cell F5.
• Insert the following formula.

• Press Enter.

• 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

We’ll concatenate all cells from column B where the respective cell in column C is not blank.

Steps:

• Select cell E5.
• Insert the following formula.

• Press Enter.

Read More: How to Concatenate Arrays in Excel

How to Concatenate If Cells Match Multiple Criteria in Excel

We are going to concatenate cells in the “Name” column with the “Score” column, but only if they are from class 11 and in team A.

Steps:

• Select cell F5.
• Insert the following formula in it.

• Press Enter.

• Click and drag the fill handle icon to the end of the column to replicate the formula for those cells.

Things to Remember

• The CONCATENATE function is an earlier version of the CONCAT. Both functions give the same result.
• The TEXTJOIN function may only be available in the newer versions of Microsoft Excel.

Download the Practice Workbook

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