How to concatenate column titles if cells in a ran

I am trying to create a list of column names based on specific criteria (if the cell contains "*").

Basically, I am trying to get Excel to automatically create the fourth column:

(1) A       B       C           List
(2) Bob*    Mike    John*       A; C
(3) Jane    Lisa*   Brenda*     B; C

*Please note that I have over 100 columns

I am a little familiar with VBA and the Concatenate function but I'm no expert.

Thanks for taking the time to read this post!

Irene

Multiple options really:

Option 1: Excel TEXTJOIN

If you have an Excel license supporting TEXTJOIN() you could use:

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH("~*",A2:C2)),A2:C2,""))

For returning values or:

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH("~*",A2:C2)),$A$1:$C$1,""))

For returning the column headers. Enter both formulas through CtrlShiftEnter and drag down.

Option 2: Google Spreadsheet TEXTJOIN

If you can use Google Spreadsheets (free to use, and if you just need to do this operation once it might be worthwhile to transfer your data) she same functions are available. It will look like:

=ARRAYFORMULA(TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH("~*",A2:C2)),A2:C2,"")))

For returning values or:

=ARRAYFORMULA(TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH("~*",A2:C2)),$A$1:$C$1,"")))

For returning column headers again.

Notice in both Option 1 and Option 2 I have used a tilde, ~, as an escape character telling excel we literally looking for an asterisk.

Option 3: Visual Basics

If you don't have an Excel license supporting TEXTJOIN() and using Google Spreadsheets is no option either, your best bet is an UDF (User Defined Function), for example:

Function CONCATENATEIF(RNG As Range, CON As String) As String

For Each CL In RNG
    If InStr(1, CL.Value, CON) > 0 Then CONCATENATEIF = CONCATENATEIF & CL.Value & ", "
Next CL
If CONCATENATEIF <> "" Then
    CONCATENATEIF = Left(CONCATENATEIF, Len(CONCATENATEIF) - 2)
End If

End Function

Call it in your worksheet like:

=CONCATENATEIF(A2:C2,"*")

And drag down... (notice this time we don't need the tilde). Likewise, if you want to return the headers simply change CONCATENATEIF = CONCATENATEIF & CL.Value & ", " into CONCATENATEIF = CONCATENATEIF & Cells(1, CL.Column).Value & ", "

Output for all options: