How To Join Texts Using TEXTJOIN & IF Functions | Microsoft Excel

The TEXTJOIN is a new function introduced in 2019 and available in Microsoft Excel 2019, Excel 2021 and Excel for M365. The TEXTJOIN function combines the text from multiple strings or range separated by delimiter.

Let’s try to understand the usage of TEXTJOIN function using below example.

The Table A have list of users and computers assigned to them and you need to report the data to management in the format given in Table B.

While we can use TEXTJOIN to combine the texts , the above requirements cannot be achieved alone with TEXTJOIN as we need to combine the data for each user separately. Hence we need to use TEXTJOIN and IF functions together to achieve the desired result.

Let’s understand the TEXTJOIN function first.

SYNTAX:

TEXTJOIN(delimiter,ignore_empty,text1,text2….)

TEXTJOIN Syntax

Let’s see how we can join texts with this function.

The result:

The below example show how you can join the text from multiple cells into a single cell separating them by comma.

Here is the result. All the values from B2:B6 combined in D2. All values were separated by comma which we given as delimiter.

Now, try to understand IF function

The Excel IF function perform logical test on given condition and return one value for TRUE result and another value for a FALSE result.

In this example, we will understand how to use IF function to find a value and then return a value from given range.

Syntax:

IF(logical_test,[value_if_true],[value_if_false])

Now, see below example. The below formula will check all cells with in A2:A6 for value “User1”. If a match found then it will return corresponding value from B2:B6. If no match found then blank value will be returned for that row which we defined in last parameter.

Here is the result

Let’s come back to our original requirements where we need the device details for each user in single cells. We will now use both TEXTJOIN and IF function to achieve the result.

This can be achieved using below formula. We have nested IF function inside TEXTJOIN. The IF function will return all matching value for a user1, which will then combined using TEXTJOIN function separated by comma.

Here is the result.

Now apply the above logic to original table to get the desired result.

The IF function will search the value in D5 in range A5:A20 and return the corresponding value from B5:B20. All the returned values will be passed to TEXTJOIN which will combines then separated by comma.

Now you can see the result in E5 for User1. To get the same result for another user, you simply need to drag or copy the formula to E6:E10.

Here is final result.

Related Posts:

• String Functions : UPPER, LOWER & PROPER | Microsoft Excel
• How To Join Texts Using TEXTJOIN & IF Functions | Microsoft Excel
• Basic Excel Functions you should know to make your work life easier
• Powershell – Merge CSV files & Insert file name as a column
• Microsoft Excel – Vlookup Explained