Thread: CONCATENATE equivalent so that A1: A11 to be?

Hi could you please post an example of what you are trying to do?

You cannot use CONCATENATE(A1:A11) in excel,. you can use a user defined VBA function.

If you press ALT and F11, then go to Insert and select Module, then paste this code:

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

then close the window and go back to the Excel sheet and use formula:

=aconcat(A1:A11)

Is that what you need?

Then it would be, =CONCATENATE(A1,A2,A3,A4,...,A11)

No I do not want VBA code. I want to excel with their commands to do this action

Why not to use VBA? It's much simpler and faster to achieve your goal. Unfortunately, there is no such a command. Thus you'll need to use CONCATENATE function with many many arguments ... as NBVC stated.