如果范围中的单元格包含特定文本/字符，如何串联列标题 [英] How to concatenate column titles if cells in a range contains a specific text/character

问题描述

我正在尝试根据特定条件创建一个列名列表（如果单元格包含 *）。

基本上，我试图让Excel自动创建第四列：

 （1）ABC清单
（2）Bob * Mike John * A; C 
（3）简丽莎*布伦达* B; C 
  

*请注意，我有超过100列

我对VBA和Concatenate函数有点熟悉，但我不是专家。

感谢您抽出宝贵的时间阅读这篇文章！

Irene

解决方案

确实有多种选择：

选项1：

或：

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:

Or:

这篇关于如果范围中的单元格包含特定文本/字符，如何串联列标题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！