Manually tracking which projects qualify for specific budgets is a tedious process for financial analysts. While teams typically rely on standard funding sources like federal allocations, state subsidies, and private endowments, consolidating this data manually leads to costly reporting errors.

Automating this with Excel grants users the ability to dynamically aggregate active funding types. Under the stipulation that your data headers remain uniform, you can deploy a robust formula solution. Leading finance teams utilize TEXTJOIN paired with IF as the primary method to consolidate these active sources. Below, we will demonstrate the exact formula structure to concatenate header names for non-blank cells.

Excel Formula To Concatenate Header Names If Cell Is Not Blank

When working with large datasets in Excel, you often encounter matrices where columns represent specific categories, tasks, attributes, or products, and rows represent individual records. A common challenge is summarizing these matrices. For instance, if you have a checklist of skills or assigned tasks across multiple columns, you might want a single summary column that lists every header where the corresponding cell is not blank.

In older versions of Excel, achieving this required tedious, nested formulas or complex VBA macros. However, with modern Excel functions like TEXTJOIN and FILTER, you can accomplish this task with a single, elegant formula. In this guide, we will explore the best methods to concatenate header names if a cell is not blank, ranging from the most modern solutions to legacy workarounds.

The Scenario: Understanding the Goal

Before diving into the formulas, let's visualize the target layout. Imagine we have a matrix tracking project assignments for team members. The columns represent different projects, and a checkmark or any character in the row indicates that the person is assigned to that project.

Our objective is to write a formula in column F that automatically looks across columns B through E, identifies which cells are not blank, and concatenates the corresponding headers from Row 1, separated by commas.

Method 1: The Modern & Dynamic Way (Excel 365 & Excel 2021)

If you are using Microsoft 365 or Excel 2021, you have access to dynamic arrays. This makes the task incredibly simple by combining the TEXTJOIN and FILTER functions.

The Formula:

=TEXTJOIN(", ", TRUE, FILTER($B$1:$E$1, B2:E2 <
>
""))

How It Works:

• FILTER($B$1:$E$1, B2:E2 < > ""): This is the engine of the formula. The FILTER function looks at the header row ($B$1:$E$1) and returns only the headers where the corresponding cell in the current row (B2:E2) is not equal to empty (< > ""). Note that we lock the header row with absolute references ($) so it doesn't shift when we copy the formula down.
• TEXTJOIN(", ", TRUE, ...): This function takes the array of headers returned by the FILTER function and glues them together.
  • The first argument (", ") defines the delimiter (a comma and a space).
  • The second argument (TRUE) tells Excel to ignore any empty values, ensuring you don't get double commas if some columns are empty.

Pros: Extremely clean, handles an infinite number of columns effortlessly, and adapts dynamically if columns are added or removed.

Method 2: The Semi-Modern Way (Excel 2019)

If you have Excel 2019, you have the TEXTJOIN function, but you do not have the FILTER function. In this case, you can use a logical IF statement inside TEXTJOIN as an array formula.

The Formula:

=TEXTJOIN(", ", TRUE, IF(B2:E2 <
>
"", $B$1:$E$1, ""))

Note: If you are in Excel 2019 or older, you must press Ctrl + Shift + Enter instead of just Enter to commit this as an array formula. When done correctly, Excel will wrap the formula in curly braces { }.

How It Works:

• The IF(B2:E2 < > "", $B$1:$E$1, "") checks each cell in the row. If the cell is not blank, it returns the header from the corresponding column. If it is blank, it returns an empty string ("").
• This results in an array like: {"Project Alpha", "", "Project Gamma", ""}.
• TEXTJOIN then takes this array, ignores the empty strings (thanks to the TRUE argument), and joins the remaining headers with a comma and a space.

Method 3: The Legacy Way (Excel 2016 and Older)

If you are working with an older version of Excel that lacks the TEXTJOIN function, you cannot easily loop through arrays. Instead, you have to manually evaluate each column using string concatenation (& ) and conditional IF statements.

The Formula:

=SUBSTITUTE(TRIM(IF(B2<
>
"", $B$1&
" ","") &
IF(C2<
>
"", $C$1&
" ","") &
IF(D2<
>
"", $D$1&
" ","") &
IF(E2<
>
"", $E$1&
" ","")), " ", ", ")

How It Works:

Because we cannot use a; we have to build the; manually:

1. Each IF(B2<>"", $B$1&" ","") checks a single cell. If it's not blank, it appends the header name followed by a single space.
2. We concatenate these values using the & operator. For John Doe, this produces "Project Alpha Project Gamma ".
3. The TRIM function removes any extra spaces, including trailing or leading spaces, resulting in "Project Alpha Project Gamma".
4. Finally, SUBSTITUTE replaces the spaces between words with a comma and space (", ").

Warning: This legacy method works best if your headers are single words. If your headers contain spaces (like "Project Alpha"), TRIM and SUBSTITUTE will break the internal spaces of the header. For multi-word headers in older Excel versions, you must construct a tedious chain of nested substitutions or use a custom VBA function.

Advanced Tweak: Filtering Based on Specific Values

Sometimes, a cell might not be "blank," but it contains a value you want to ignore, such as "No", "N/A", or "0". You can easily modify the Modern Excel formula (Method 1) to target specific values.

For example, if you want to concatenate headers only when cells contain the word "Yes":

=TEXTJOIN(", ", TRUE, FILTER($B$1:$E$1, B2:E2 = "Yes"))

If you want to concatenate headers where cells contain a number greater than 0:

=TEXTJOIN(", ", TRUE, FILTER($B$1:$E$1, (ISNUMBER(B2:E2)) * (B2:E2 > 0)))

Summary: Which Method Should You Use?

Always choose your formula based on your Excel version to ensure compatibility across your team:

• Use Method 1 (TEXTJOIN + FILTER) if you and your team are entirely on Microsoft 365 or Excel 2021+. It is the cleanest, shortest, and most robust option.
• Use Method 2 (TEXTJOIN + IF) if some team members are on Excel 2019.
• If your workbook must run on legacy versions (Excel 2013/2010), consider upgrading the sheet or using a lightweight VBA User Defined Function (UDF) to mimic the behavior of TEXTJOIN.