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.


Download Practice Workbook

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


5 Suitable Examples to Concatenate Cells with If Condition in Excel

We can concatenate 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.

excel concatenate if condition with ampersand and if

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.

excel concatenate if condition by combining ampersand and if condition

  • 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 with the if condition easily in Excel with the help of the ampersand sign.

Read More: How to Concatenate Names in Excel (5 Easy Ways)


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.

excel concatenate if condition by combining concat and if functions

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

Read More: How to Combine Text from Two or More Cells into One Cell in Excel (5 Methods)


Similar Readings


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.

excel concatenate if condition new 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.

excel concatenate if condition using textjoin and if functions

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 Multiple Cells With Space in Excel (7 Methods)


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.

excel concatenate if condition of another column

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

Read More: How to Concatenate Multiple Cells with Comma in Excel (4 Ways)


Similar Readings


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. If you don’t have one, click here to see how 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.

opening vba window

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

inserting module in vba

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

excel concatenate if condition using custom vba functions

  • 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: Concatenate Multiple Cells Based on Criteria in Excel (4 Methods)


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.

excel concatenate if not blank condition

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

Read More: Concatenate Multiple Cells but Ignore Blanks in Excel (5 Ways)


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.

excel concatenate if condition with multiple criteria

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

excel concatenate if condition dragging fill handle for multiple criteria

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

Read More: How to Concatenate Multiple Cells in Excel (7 Easy Ways)


πŸ’¬ 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.

For more guides like this, visit Exceldemy.com.


Related Articles

Abrar-ur-Rahman Niloy

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo