Concatenate cells in a COL in between blank cells.

[brashandcrass]

Found a few options using VBA but that's a realm I'm not familiar with. Is there a formula/function someone could recommend?

I found something like this...

=CONCATENATE(A1,IF(ROWS(A1)=COUNTBLANK(A1),A2, IF(ISBLANK(A2),"","," & A2)),IF(ROWS(A1:A2)=COUNTBLANK(A1:A2),A3, IF(ISBLANK(A3),"","," & A3)),IF(ROWS(A1:A3)=COUNTBLANK(A1:A3),A4, IF(ISBLANK(A4),"","," & A4)))

but it doesn't account for "groups" of cells longer than 4.

Thanks.

 

[Yongle]

Try this

=TEXTJOIN(",",TRUE,A1:A1000)

Ist attribute is the delimiter
Empty cells are ignored with 2nd attribute set to TRUE
3rd atttribute is the range

To piecemeal ranges, separate them with a comma

=TEXTJOIN(",",TRUE,A1:A5,A10:A20)

 

[brashandcrass]

Pardon, let me clarify: I mean if I have a COL like this:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;"> | A |
+-------+
| Text A|
| Text A|
| Text A|
| |
</code><code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;"><code style="font-style: inherit; font-variant: inherit; font-weight: inherit; white-space: inherit; margin: 0px; padding: 0px; border: 0px; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit;">| Text B|
| Text B|
| Text B|
| |
</code><code style="font-style: inherit; font-variant: inherit; font-weight: inherit; white-space: inherit; margin: 0px; padding: 0px; border: 0px; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit;"><code style="font-style: inherit; font-variant: inherit; font-weight: inherit; white-space: inherit; margin: 0px; padding: 0px; border: 0px; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit;">| Text C|
| Text C|
| Text C|
| |
</code><code style="font-style: inherit; font-variant: inherit; font-weight: inherit; white-space: inherit; margin: 0px; padding: 0px; border: 0px; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit;"><code style="font-style: inherit; font-variant: inherit; font-weight: inherit; white-space: inherit; margin: 0px; padding: 0px; border: 0px; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit;">| Text D|
| Text D|
| Text D|
</code><code style="font-style: inherit; font-variant: inherit; font-weight: inherit; white-space: inherit; margin: 0px; padding: 0px; border: 0px; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit;">
</code></code></code><code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">I would get a COL like this:

<code style="font-style: inherit; font-variant: inherit; font-weight: inherit; white-space: inherit; margin: 0px; padding: 0px; border: 0px; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit;">| A |
+-------+
| Text A, Text A, Text A|
| Text B, Text B, Text B|
| Text C, Text C, Text C|
| Text D, Text D, Text D|</code></code></code>

 

[nardagus]

Function TEXTJOIN was introduced in Excel 2016 and above (plus Office 365). So if you use older excel, there is no other way but stick to VBA. At least I didn't find such a formula.
However you can have UDF (user defined function which, in case on excel is nothing else but VBA code ) you can use as any other function in excel. All you need to do is to place a code in your excel personal template.

I haven't search for such a solution on MrExcel forum, but I'm sure someone has already posted it. And if not I saw such UDF on couple of websites.