Excel.Tips.Net ExcelTips (Menu Interface)

Combining Columns

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Combining Columns.

There may be times when you have a need to concatenate cells together. For instance, you may have information in three columns, but you want it combined together into the first column of each row. The following macro, StuffTogether, will do just that. It examines the range of cells you select, and then moves everything from each cell in a row into the first cell of the row.

Sub StuffTogether()
    Dim FirstCol As Integer, FirstRow As Integer
    Dim ColCount As Integer, RowCount As Integer
    Dim ThisCol As Integer, ThisRow As Integer
    Dim J As Integer, K As Integer
    Dim MyText As String

    FirstCol = ActiveWindow.RangeSelection.Column
    FirstRow = ActiveWindow.RangeSelection.Row
    ColCount = ActiveWindow.Selection.Columns.Count
    RowCount = ActiveWindow.Selection.Rows.Count

    For J = 1 To RowCount
        ThisRow = FirstRow + J - 1
        MyText = ""
        For K = 1 To ColCount
            ThisCol = FirstCol + K - 1
            MyText = MyText & Cells(ThisRow, ThisCol).Text & " "
            Cells(ThisRow, ThisCol).Value = ""
        Next K
        MyText = Trim(MyText)
        Cells(ThisRow, FirstCol).Value = MyText
    Next J
End Sub

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2116) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Combining Columns.

Related Tips:

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!


Leave your own comment:

  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*What is 5+3 (To prevent automated submissions and spam.)
           Commenting Terms

Comments for this tip:

David Lewis    21 Jul 2012, 08:18
In this hint you use 'concatenate' but don't use this function, which exists in Excel. Here is the formula which combines columns A and B =CONCATENATE(A2," ",B2) The " " inserts a space. [John Smith]

The end result is as a formula but shows the viewer the content.
Once you have done the first row you just apply the formula to the rows beneath. If you want it to be saved as an actual name you copy the column, use paste special and choose 'values'. Of course you then have to delete the unnecessary columns (although in my case I wanted a letter to start 'Dear John' but use 'John Smith' elsewhere so I needed columns for first name, last name, concatenated name).

[I needed to send form letters to a group, so I used Mail Merge in Word, selecting the data from an Excel spreadsheet.]

I don't use macros and formulas much but my guess is that the resulting macro would be simpler even if you still have to use row count.

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us


Advertise with Us

Our Privacy Policy

Our Sites


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.