Macro to Join Text in Excel

These VBA macros join the text from several cells into one: the whole selection into a single cell, or each row into a cell at the end of the row. There’s also a custom function you can use in formulas.

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Join the selection into one cell

Select the cells, run the macro, then click the cell for the result:

Sub JoinSelection()
    Dim c As Range, s As String, out As Range
    For Each c In Selection
        If c.Text <> "" Then s = s & ", " & c.Text
    Next c
    If s = "" Then Exit Sub
    Set out = Application.InputBox("Click the cell for the result:", Type:=8)
    out.Value = Mid(s, 3)
End Sub

Blank cells are skipped. Change ", " to " " or " | " for a different separator. Mid(s, 3) removes the separator at the start.

Join each row

Puts the joined text of each selected row in the column just to the right of the selection:

Sub JoinEachRow()
    Dim r As Range, c As Range, s As String
    For Each r In Selection.Rows
        s = ""
        For Each c In r.Cells
            If c.Text <> "" Then s = s & " " & c.Text
        Next c
        r.Cells(1, r.Columns.Count + 1).Value = Trim(s)
    Next r
End Sub

Handy for joining first, middle and last names.

A JOINTEXT function for formulas

Paste this into a module, then use =JOINTEXT(A2:C2) or =JOINTEXT(A2:A20, "; ") in any cell:

Function JOINTEXT(rng As Range, Optional sep As String = ", ") As String
    Dim c As Range
    For Each c In rng
        If c.Text <> "" Then JOINTEXT = JOINTEXT & sep & c.Text
    Next c
    JOINTEXT = Mid(JOINTEXT, Len(sep) + 1)
End Function

Without a macro

Excel 2019 and later have TEXTJOIN, which does the same:

Things to know

• c.Text uses the value as displayed, so dates and numbers keep their formatting.
• A cell can hold up to 32,767 characters.
• See also combine first and last name.

Related macros