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]

Thanks guys! Appreciate it!

You are welcome. You might be interested in this single UDF which combines both functions into one (just feed it a contiguous vertical or a horizontal range and the function will figure out which it is on its own)(it also corrects the error message generated if a single cell is passed in)...

Function Concat(Rng As Range, Optional Delimiter As String) As String
  If Rng.Columns.Count > 1 Then
    Concat = Join(WorksheetFunction.Index(Rng.Value, 1, 0), Delimiter)
  ElseIf Rng.Rows.Count > 1 Then
    Concat = Join(WorksheetFunction.Transpose(Rng), Delimiter)
  Else
    Concat = Rng.Value
  End If
End Function

 

[anthonyexcel]

Appreciate that Rick! Thank you again!