How to modify a function so that matches are appended in a new line in Excel formula

In Excel, you can use the CHAR function to insert special characters, such as line breaks, tabs, or symbols, into a cell or a formula. The CHAR function takes a numeric code as an argument and returns the corresponding character. For example, =CHAR(10) returns a line break, =CHAR(9) returns a tab, and =CHAR(65) returns the letter A.

You can use the CHAR function to modify a function so that matches are appended in a new line, instead of being separated by a delimiter, such as a comma or a semicolon. This can make the output more readable and organized, especially when there are many matches.

Procedures

To modify a function so that matches are appended in a new line, you need to do the following steps:

• Identify the function that you want to modify and the range of cells that contain the matches.
• Use the & operator to concatenate the CHAR function with the value of the match. For example, =CHAR(10)&A1 returns the value of A1 with a line break before it.
• Use the IF function to check if the match meets the criteria, and return the concatenated value if true, or an empty string if false. For example, =IF(A1="Yes",CHAR(10)&A1,"") returns the value of A1 with a line break before it if A1 is equal to “Yes”, or nothing otherwise.
• Use the SUMPRODUCT function to combine the results of the IF function for all the cells in the range. The SUMPRODUCT function takes one or more arrays as arguments and returns the sum of the products of the corresponding elements. In this case, the arrays are the results of the IF function, which are either text values or empty strings. The SUMPRODUCT function treats text values as 0 and empty strings as 1, and multiplies them together. This effectively returns the text values and ignores the empty strings. For example, =SUMPRODUCT(IF(A1:A5="Yes",CHAR(10)&A1:A5,"")) returns the values in A1:A5 that are equal to “Yes”, each with a line break before it, and ignores the rest.
• Use the MID function to remove the first line break from the result of the SUMPRODUCT function. The MID function takes a text value, a starting position, and a number of characters as arguments, and returns a substring of the text value. In this case, the text value is the result of the SUMPRODUCT function, the starting position is 2, and the number of characters is the length of the text value minus 1. This effectively removes the first character, which is the line break, from the text value. For example, =MID(SUMPRODUCT(IF(A1:A5="Yes",CHAR(10)&A1:A5,"")),2,LEN(SUMPRODUCT(IF(A1:A5="Yes",CHAR(10)&A1:A5,"")))-1) returns the same result as the previous formula, but without the first line break.

Explanation

To illustrate the procedures, let’s use an example scenario. Suppose you have a table of data in Excel, as shown below:

You want to create a function that returns the names of the people who are single, each on a new line, in cell G1. You can use the following formula:

=MID(SUMPRODUCT(IF(D2:D6="Single",CHAR(10)&B2:B6,"")),2,LEN(SUMPRODUCT(IF(D2:D6="Single",CHAR(10)&B2:B6,"")))-1)

Let’s break down this formula and see how it works.

• First, the IF function checks if the values in D2:D6 are equal to “Single”, and returns the values in B2:B6 with a line break before them if true, or an empty string if false. The result is an array of text values or empty strings, as shown below:

• Next, the SUMPRODUCT function takes the array of text values or empty strings as an argument and returns the sum of the products of the corresponding elements. Since the text values are treated as 0 and the empty strings are treated as 1, the SUMPRODUCT function effectively returns the text values and ignores the empty strings. The result is a text value that contains the names of the people who are single, each with a line break before them, as shown below:

• Finally, the MID function takes the text value that contains the names of the people who are single, each with a line break before them, as an argument, and returns a substring of the text value that starts from the second character and has the length of the text value minus 1. This effectively removes the first character, which is the line break, from the text value. The result is a text value that contains the names of the people who are single, each on a new line, as shown below:

The final result is displayed in cell G1, as shown below:

Other approaches

There are other ways to modify a function so that matches are appended in a new line in Excel formula. Here are some alternative approaches:

• You can use the TEXTJOIN function instead of the SUMPRODUCT function to join the text values with a delimiter. The TEXTJOIN function takes a delimiter, an ignore empty argument, and one or more text values or ranges as arguments, and returns a text value that joins the text values or ranges with the delimiter. If the ignore empty argument is TRUE, the TEXTJOIN function ignores the empty values. In this case, you can use the CHAR function as the delimiter, and set the ignore empty argument to TRUE. For example, =TEXTJOIN(CHAR(10),TRUE,IF(D2:D6="Single",B2:B6,"")) returns the same result as the SUMPRODUCT function in the previous approach.
• You can use the FILTER function instead of the IF function to filter the values that meet the criteria. The FILTER function takes an array, an include argument, and an optional if empty argument as arguments, and returns the filtered array based on the include argument. If the include argument is an array of logical values, the FILTER function returns the corresponding values from the array that are TRUE. If the include argument is a single logical value, the FILTER function returns the entire array if TRUE, or an error if FALSE. In this case, you can use the FILTER function to return the values in B2:B6 that are equal to “Single”, and use the CHAR function as the if empty argument. For example, =FILTER(B2:B6,D2:D6="Single",CHAR(10)) returns the same result as the IF function in the previous approach.