This site will look better if you upgrade to a browser that supports web standards.

Merging data to a column or cell

Merging data in multiple columns into one.

The Data/Text to Columns function is very handy for splitting text in one column into several columns. There's no built-in function for doing the opposite, though.

This macro will merge the values in each row of the Selection. If you want to insert a delimiter, change the sDELIM constant, or insert a column and array enter the delimiter for the appropriate number of rows before merging).

    '**************************************************************************
    'Purpose:   Merge values in a multi-column array into the first column
    'Inputs:    Selection
    'Returns:   Merged values in column 1 of rRng
    '**************************************************************************
    Public Sub ColumnsToText()
        'J.E. McGimpsey http://www.mcgimpsey.com/excel/mergedata.html
        Const sDELIM As String = ""
        Dim vTxtArr As Variant
        Dim rRng As Range
        Dim nTop As Long
        Dim i As Long
        Dim j As Long
        Set rRng = Intersect(Selection, ActiveSheet.UsedRange)
        vTxtArr = rRng.Value
        nTop = UBound(vTxtArr, 1)
        For i = 1 To nTop
            For j = 2 To UBound(vTxtArr, 2)
               vTxtArr(i, 1) = vTxtArr(i, 1) & sDELIM & vTxtArr(i, j)
            Next j
        Next i
        ReDim Preserve vTxtArr(1 To nTop, 1 To 1)
        rRng.Resize(, 1).Value = vTxtArr
    End Sub

You can place this in your Personal Macro Workbook (Personal.xls for WinXL) and attach to a toolbar button or keyboard shortcut.

Or try a more flexible version, allowing you to specify the range and delimiter.

Merging the Selection into one cell

This macro merges the entire Selection into one cell. To include a delimiter, change SDELIM, or insert columns between the cells and put the delimiter in those columns (make sure the last column is a delimiter column as well.

   '**************************************************************************
    'Purpose:   Merge cells, retaining all data
    'Inputs:    Selection
    'Returns:   Merged values in the first cell of rRng
    '**************************************************************************
        Public Sub MergeToOneCell()
        'J.E. McGimpsey, http://www.mcgimpsey.com/excel/mergedata.html
        Const sDELIM As String = ", "
        Dim rCell As Range
        Dim sMergeStr As String
        With Selection
            For Each rCell In .Cells
                sMergeStr = sMergeStr & sDELIM & rCell.Text
            Next rCell
            Application.DisplayAlerts = False
            .Merge Across:=False
            Application.DisplayAlerts = True
            .Item(1).Value = Mid(sMergeStr, 1 + Len(sDELIM))
        End With
    End Sub

Or try a more flexible version, allowing you to specify the range and delimiter.

This page last updated Tuesday, 21 December 2004

McGimpsey & Associates

Home

General XL Tips

XL Formulae

Using VBA with XL

XL VBA Functions

XL VBA Macros

Mac XL

Merging data to a column or cell

Merging data in multiple columns into one.

Merging the Selection into one cell

Quick Links