Concatenate Array of Cells

austinandrei

Board Regular
Joined
Jun 7, 2014
Messages
117
Hi,
Is there a way to concatenate an array of cells? Like the one which combine CONCATENATE and TRANSPOSE formula though what I need is horizontal.
Like I will need to concatenate values from Column A to Z so ideally, the formula I need is like below:
=CONCATENATE(A1:Z1,"[]")
Unfortunately, above is not functioning.
I am using Excel 2010 so TEXTJOIN formula is not existing.
Array Formula is welcome, not the VBA.
As I want this to be useful for any case like if the cells to be concatenated is D5:S5 and others.
Thanks!
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sorry,I don't want the exact function of CONCATENATE then using F9 since I would like to drop the formula downwards and if possible, if the last cell is the last to concatenate, then there will be no separator like if my data are below:
Column AColumn BColumn CColumn DColumn EColumn F
123451;2;3;4;5
abcdea;b;c;d;e
ohyeso;h;y;e;s

<tbody>
</tbody>

The formula is in Column F and that the separator is ";"
As mentioned above, the number of columns and the start of column and row is variable when concatenating.
Thanks!
 
Upvote 0
In F1 control+shift+enter, not just enter, and copy down:

=REPLACE(aconcat(";"&A1:E1),1,1,"")

For this formula to work, add the following code for ACONCAT as a Module to your workbook using Alt+F11...

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function
 
Upvote 0
Hi Aladin,
Thanks for the reply.
Is there a way that I won't be using VBA? I actually want to have an array formula that can be used in any excel workbook wherein I won't need to code and can just share the formula to my other colleagues if they need it. Just an exact function of TEXTJOIN formula in Excel 2016 and newer versions that I am seeing on the net, though I am only using an older version.
 
Last edited:
Upvote 0
With ACOUNCAT udf, you he the TEXTJOIN functionality.

For the 5 cells, you can also have...

In F1 just enter and copy down:

=REPLACE(IF($A1="","",";"&$A1)&IF($B1="","",";"&$B1)&IF($C1="","",";"&$C1)&IF($D1="","",";"&D1)&IF($E1="","",";"&$E1),1,1,"")
 
Upvote 0
Yes, I mean a TEXTJOIN function without using VBA as ACOUNCAT will still need to code in module. I can't instruct everyone in our pool on how to do the coding. Also, the reason why I need a concatenation of array cells as it will not be limited to 5 columns. Original, as I mentioned I want to concatenate data from A to Z column then just drop the formula on the rest of the row. It can be from A to ZZ or just E to W. That is why I was looking for an array formula that can use an array and just use the formula casually in any excel without going into modules.
 
Last edited:
Upvote 0
There is nothing native except TEXTJOIN. If you don.t have TEXTJOIN and you have a lot of cells to join, you need a udf like ACONCAT with the accompanying array formula I have provided.
 
Upvote 0
Ok. Still many thanks Aladin. I thought a simple array formula could be formulated to have this function.

An array formula should be with CONCATENATE, but CONCATENATE is not designed to act on an array, i.e. on an {},. Hence TEXTJOIN addition of recent times.
 
Upvote 0
Sorry, I'm gonna make this one active again as it is related.
I found this on google which is a simple function formula, not using CSE as I need to use it on other codes:
Code:
Public Function ColumnsToText(rng As Range, Optional DelimitBy As String = " ") As String
'Applies on a row/partial row of data. Must be continuous cells, e.g., A1:D1.
Dim var As Variant


var = Application.Transpose(Application.Transpose(rng.Value))
    ColumnsToText = Join(var, DelimitBy)


End Function
Unfortunately, it seems that it has limitations on a very long cell value unlike when using the "&" formula like "=A1&","&B1&","&C1", "=ColumnsToText(A1:C1,",")" is not working properly. But for others, it is really working, just not on very long value of cells. Can this be fix on not having limitations on the number of characters in a cell?
Thanks!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top