Concatenate rows

[ganeshpoojary05]

Hi,

I need a concatenate formula,
for example: I have a data which contains email id of all the employees in Range (a1:a100). I need a formula where i can get consolidation of all email id from range (a1:a100).

For now I'm using the below formula:
=concatenate(a1,a2,a3,...a100).

Any help would be greatly appreciated.

 

[Rick Rothstein]

No delimiter between the text in the cells?

How about a UDF (user defined function) which allows you to specify a delimiter if you want (this UDF only works for vertical contiguous ranges)?

Function Concat(Rng As Range, Optional Delimiter As String) As String
  Concat = Join(WorksheetFunction.Transpose(Rng), Delimiter)
End Function

To install this code, press ALT+F11 to go into the VB editor, then click Insert/Module on its menu bar. Now copy/paste the above code into the code window that opened up. That's it. Go back to your worksheet and enter this formula into any unused cell...

=Concat(A1:A100)

That will duplicate what your CONCATENATE formula does. If you wanted to put delimiter between the cell values, say as comma-space, you would do this...

=Concat(A1:A100,", ")

 

[ganeshpoojary05]

Thanks that works great!!! but is there any formula where I can use this directly.

 

[Rick Rothstein]

...but is there any formula where I can use this directly.

I'm not sure I understand your question... I gave you a formula where you can use it directly...

=Concat(A1:A100)

or

=Concat(A1:A100,", ")

And you can use Concat just like any built-in function, embedding it within other function calls if you need that kind of functionality. If, on the other hand, you are asking if there is a way to do what you want without using VB, well other than the CONCATENATE function call you posted originally, no, I don't think there is. Excel is a number cruncher at heart, so while it has some text functions, it is not really a word processor... some actions are cumbersome to do with formulas and concatenation is one of them.

 

[ganeshpoojary05]

thanks again...

 

[anthonyexcel]

No delimiter between the text in the cells?

How about a UDF (user defined function) which allows you to specify a delimiter if you want (this UDF only works for vertical contiguous ranges)?

Function Concat(Rng As Range, Optional Delimiter As String) As String
  Concat = Join(WorksheetFunction.Transpose(Rng), Delimiter)
End Function

To install this code, press ALT+F11 to go into the VB editor, then click Insert/Module on its menu bar. Now copy/paste the above code into the code window that opened up. That's it. Go back to your worksheet and enter this formula into any unused cell...

=Concat(A1:A100)

That will duplicate what your CONCATENATE formula does. If you wanted to put delimiter between the cell values, say as comma-space, you would do this...

=Concat(A1:A100,", ")

Rick,

What would be the approach using the Join Function for horizontal data? i.e A2, B2

 

[anthonyexcel]

Rick,

This is what I came up with. Is this the right approach to take?

Function concat(r As Range, Optional delimeter As String) As String
concat = Join(Application.Transpose(Application.Transpose(r)), delimeter)
End Function

 

[Rick Rothstein]

Rick,

What would be the approach using the Join Function for horizontal data? i.e A2, B2

Rick,

This is what I came up with. Is this the right approach to take?

Function concat(r As Range, Optional delimeter As String) As String
concat = Join(Application.Transpose(Application.Transpose(r)), delimeter)
End Function

Here are both functions (the first renamed appropriately and the second being the one you just requested)..

Function ConcatVert(Rng As Range, Optional Delimiter As String) As String
  ConcatVert = Join(WorksheetFunction.Transpose(Rng), Delimiter)
End Function
 
Function ConcatHorz(Rng As Range, Optional Delimiter As String) As String
  ConcatHorz = Join(WorksheetFunction.Index(Rng.Value, 1, 0), Delimiter)
End Function

The method you posted would work but it uses two calls to the worksheet whereas the one I posted only uses one call.

 

[mikerickson]

Alternatly, one could use a formula like

=Concat(TRANSPOSE(A1:AZ1), ",")

 

[anthonyexcel]

Thanks guys! Appreciate it!