0
196
VistasHow to concatenate column values in second row if cells in a range contains a specific text/character
So, basically, I asked a similar question to this in 2019 and got a great answer from JvdV. You can see it here: https://stackoverflow.com/a/56467170/19386858. But I can't DM him/her/them, so I'm asking everyone.
I am trying to create a list of based on specific criteria (if the cell contains "*"). BUT instead of using the title row (row 1), I want to use the values in row 2. Basically, I added another row on top of my original table. So, what was row 1 is now row 2. JvdV gave me a UDF to put in the VBA in my original question here: https://stackoverflow.com/a/56467170/19386858. Can someone edit it so that the text in row 2 gets concatenated?
Basically, I am trying to get Excel to automatically create the fourth column:
A B C D
(1)
(2) text1 Text2 Text3 List
(3) Bob* Mike John* Text1; Text3
(4) Jane Lisa* Brenda* Text2; Text3
Thanks
2 Respuestas
Responde la pregunta0
Do you need to solve this in VBA? If you have Excel 365, the formula is fairly straightforward - you could, for example, MAP the array, looking for an asterix, and then TEXTJOIN the resulting map. Something like this:
=TEXTJOIN(";",TRUE, MAP(A3:C3,LAMBDA(v,IF(RIGHT(v,1)="*", INDEX($A$2:$C$2,1, COLUMN(v)),""))))
0
You can try below formula-
=TEXTJOIN(";",TRUE,FILTER($A$2:$C$2,ISNUMBER(SEARCH("~*",A3:C3))))
If your version of excel support LAMBDA() function then try below formula for one go.
=BYROW(A3:C4,LAMBDA(a,TEXTJOIN(";",TRUE,FILTER($A$2:$C$2,ISNUMBER(SEARCH("~*",a))))))
