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

`=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**.

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

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

`=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 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**.

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

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

`=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.

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

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

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

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

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

`=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.

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

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

** Breakdown of the Formula**

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

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

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

- 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