An Excel macro to concatenate values in ranges of cells

Introduction

The concatenate function in Excel seems very limited. Maybe I'm not using it correctly or there's a clever way of concatenating ranges of cells that I don't know but having to click on every cell that you want to concatenate just seems a waste of time.

I therefore thought that a better solution would be to add a function that automatically concatenates ranges of cells. Better yet, you can specify a separator for the cells if, for example, you wanted to output a range of semi-colon delimited data rather than just comma or tab delimited.

Macro

Anyway here's the macro:

-----------------------------------------------------------------------------

Function ConcatenateRange(d As Range, Optional separator As String) As String

    Dim s As String

    Dim i, j As Integer

    

    s = ""

    For i = 1 To d.Columns.Count

        For j = 1 To d.Rows.Count

            s = s + d.Cells(j, i).Text + separator

        Next

    Next

    

    s = Left(s, Len(s) - Len(separator))

    

    ConcatenateRange = s

End Function

-----------------------------------------------------------------------------

Example

Say you had a list of data spread over six cells from A1 to F1:

The function will join the cells, separated by the semi-colon.