How to concatenate custom cells in Excel

Example 5 - Items with Line BreaksIn this example, TEXTJOIN is combined with FILTER, and other functions, to create a list of order details, all in one cell, with line breaks.The o

How to concatenate custom cells in Excel

Example 5 - Items with Line Breaks

In this example, TEXTJOIN is combined with FILTER, and other functions, to create a list of order details, all in one cell, with line breaks.

list of items for selected order with line breaks

The order details are pulled from a named Excel table, Sales_Data.

Sales_Data table

TEXTJOIN/FILTER Formula

On the OrderInfo sheet, there is a drop down in cell B3, where you can select an Order number.

Here is the formula in cell B4, which lists all the items from the selected order. The formula is colour coded to show the different functions, and there are details below on how the formula works.

  • =SUBSTITUTE(TEXTJOIN(", ",FALSE, FILTER(CHOOSE({1,2,3,4}, Sales_Data[Category], Sales_Data[Product], Sales_Data[Grams], Sales_Data[Quantity]&CHAR(10)), Sales_Data[Order]=B3)), CHAR(10) & ", ",CHAR(10))

A) FILTER and CHOOSE

In the formula, FILTER returns records where the order number matches the order number in cell B3.

  • FILTER(CHOOSE({1,2,3,4}, Sales_Data[Category], Sales_Data[Product], Sales_Data[Grams], Sales_Data[Quantity]&CHAR(10)), Sales_Data[Order]=B3)

With the CHOOSE function, 4 columns are selected for the FILTER

  • For the first argument, there's an array of 4 numbers -{1,2,3,4}
  • Next, these 4 columns are selected: Category, Product, Grams, Quantity
  • Quantity is combined with CHAR(10) -- a line break

B) TEXTJOIN

Next, TEXTJOIN combines the FILTER results, with a comma and space character as the delimiter. Ignore blanks is set to FALSE

  • TEXTJOIN(", ",FALSE, FILTER(CHOOSE({1,2,3,4}, Sales_Data[Category], Sales_Data[Product], Sales_Data[Grams], Sales_Data[Quantity]&CHAR(10)), Sales_Data[Order]=B3))

C) SUBSTITUTE

Finally, SUBSTITUTE cleans up the TEXTJOIN result. To remove extra delimiters, it replaces any "line break comma space", with a line break

  • =SUBSTITUTE(TEXTJOIN(", ",FALSE, FILTER(CHOOSE({1,2,3,4}, Sales_Data[Category], Sales_Data[Product], Sales_Data[Grams], Sales_Data[Quantity]&CHAR(10)), Sales_Data[Order]=B3)), CHAR(10) & ", ",CHAR(10))

Video liên quan