Excel Formulas to Combine Product Names and Corresponding Quantities

Manually merging product names with their corresponding quantities in Excel is a tedious, error-prone struggle for inventory and finance professionals. While standard funding sources and operational budgets are typically earmarked for expensive database software to resolve these issues, leveraging native Excel formulas grants immediate reporting efficiency without the added overhead.

This technique is highly effective, with the stipulation that your source dataset maintains a consistent tabular layout. For example, seamlessly combining "Standard Widget" with its quantity "250" into "Standard Widget (250)" dramatically improves dashboard readability. Below, we outline the exact formula structure required to automate this consolidation process.

When managing inventory, processing sales orders, or creating packing slips in Excel, you often run into a common data-formatting challenge: you have one column containing product names and another containing their corresponding quantities, but you need to combine this information into a single, clean, and easily readable format.

Whether you want to generate a row-by-row label (like "5x Blue Widget") or compile an entire order into a single, comma-separated summary cell (such as "Blue Widget (5), Red Gadget (3), Green Tool (1)"), Excel offers several powerful formulas to get the job done. This guide will walk you through the best methods to combine product names with their correlating quantities, starting from basic concatenation to advanced dynamic array formulas that automatically filter out zero values.

The Sample Data Setup

To illustrate these techniques, let us assume we have a simple spreadsheet structure with the following data in columns A and B:

Method 1: Row-by-Row Combination Using the Ampersand (&) Operator

If you simply want to create a new column (Column C) that combines the product name and quantity for each individual row, the easiest approach is using the ampersand (&) operator. This operator acts as a glue to join text strings together.

Option A: Quantity First ("12x Wireless Mouse")

To display the quantity followed by an "x" and the product name, enter the following formula in cell C2 and drag it down:

=B2 & "x " & A2

How it works: Excel takes the value in cell B2 (12), appends the literal text string "x " (with a trailing space), and then appends the value in cell A2 ("Wireless Mouse"). The result is: 12x Wireless Mouse.

Option B: Product First with Parentheses ("Wireless Mouse (12)")

If you prefer to list the product name first with the quantity enclosed in parentheses, use this variation:

=A2 & " (" & B2 & ")"

The result for the first row will be: Wireless Mouse (12).

Method 2: Combining an Entire List into a Single Cell Using TEXTJOIN

Often, you do not want to keep the data on separate rows. Instead, you might want to summarize an entire customer order in a single cell to copy and paste into an email or invoice. In modern versions of Excel (Excel 2019, Excel 365, and Excel for the Web), the TEXTJOIN function is the absolute best tool for this job.

The syntax for TEXTJOIN is:

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

To combine all products and quantities from our table into a single, comma-separated sentence, we can combine TEXTJOIN with range operations:

=TEXTJOIN(", ", TRUE, B2:B5 & "x " & A2:A5)

How it works:

• B2:B5 & "x " & A2:A5 is an array operation. Excel temporarily creates a list in its memory that looks like this: {"12x Wireless Mouse", "0x Mechanical Keyboard", "45x USB-C Cable", "7x HDMI Switch"}.
• TEXTJOIN takes that array, uses ", " (comma and space) as the separator, and ignores any empty values (due to the TRUE argument).
• The final output in your cell will be: 12x Wireless Mouse, 0x Mechanical Keyboard, 45x USB-C Cable, 7x HDMI Switch.

Method 3: Filtering Out Zero Quantities (The Clean Summary)

In the previous example, you probably noticed a glaring issue: 0x Mechanical Keyboard was included in our final text string. If a product has a quantity of zero, we usually want to omit it from our invoice or order summary completely.

If you are using Excel 365 or Excel 2021, you can combine TEXTJOIN with the dynamic FILTER function to clean up your output effortlessly.

The Formula:

=TEXTJOIN(", ", TRUE, FILTER(B2:B5 & "x " & A2:A5, B2:B5 > 0, "No items selected"))

Step-by-Step Breakdown:

1. B2:B5 & "x " & A2:A5: Just like before, this creates our concatenated array of products and quantities.
2. FILTER(..., B2:B5 > 0): This is the magic step. The FILTER function evaluates the quantity range (B2:B5). It only allows items to pass through to the next step if their corresponding quantity is strictly greater than zero. Because the "Mechanical Keyboard" has a quantity of 0, it is stripped out of the array entirely.
3. "No items selected": This is the fallback argument for the FILTER function. If every single item in your list has a quantity of 0, the formula will cleanly output "No items selected" instead of a frustrating #CALC! error.
4. TEXTJOIN(", ", TRUE, ...): Finally, TEXTJOIN takes the filtered array-which now only contains non-zero items-and glues them together with commas.

The result: 12x Wireless Mouse, 45x USB-C Cable, 7x HDMI Switch. Note how the zero-quantity keyboard has vanished!

Method 4: Dynamic Formatting for Large or Growing Lists

If you regularly add new rows to your product list, using static ranges like A2:A5 means you have to constantly update your formulas. To avoid this, convert your data range into an official Excel Table, or use dynamic references.

Using Excel Tables (Recommended)

Select your data range (A1:B5), and press Ctrl + T to turn it into a table. Let's assume your table is named OrderTable. You can now write a structured reference formula that automatically expands as you add new rows:

=TEXTJOIN(", ", TRUE, FILTER(OrderTable[Quantity] & "x " & OrderTable[Product Name], OrderTable[Quantity] > 0, ""))

Now, whenever you add a new product to the bottom of your table, the combined cell will immediately update without you ever having to rewrite the formula.

Handling Legacy Excel Versions (Without TEXTJOIN or FILTER)

If you are using an older version of Excel (Excel 2016 or earlier), you will not have access to TEXTJOIN or FILTER. However, you can still achieve a row-by-row combination easily using the standard CONCATENATE function (or the & operator as shown in Method 1).

If you need to combine multiple rows into a single cell in older Excel versions without writing complex VBA code, you will have to build a "concatenation chain."

1. In cell C2, enter: =IF(B2>0, B2 & "x " & A2, "")
2. In cell C3, enter: =IF(B3>0, IF(C2="", "", C2 & ", ") & B3 & "x " & A3, C2)
3. Drag the formula in C3 down to the bottom of your dataset. The very last cell in Column C will contain your fully compiled, comma-separated list, successfully omitting any zero-value rows.

Summary and Best Practices

• Use the Ampersand (&) for simple, row-by-row combinations. It is lightweight, fast, and works in every version of Excel ever made.
• Use TEXTJOIN when you need to collapse multiple rows of data into a single, readable string inside a single cell.
• Always combine FILTER with TEXTJOIN to ensure that your lists remain clean and do not include confusing "0x" entries for unpurchased or out-of-stock items.
• Turn your data ranges into Tables (Ctrl + T) to keep your formulas dynamic and future-proof against new inventory additions.

By mastering these simple combinations of text manipulation and array logic, you can transform raw, tabular data into highly readable, presentation-ready product summaries in seconds.