ExcelRibbon.Tips.Net ExcelTips (Ribbon Interface)

Combining Multiple Rows in a Column

Please Note: This article is written for users of the following Microsoft Excel versions: 2007 and 2010. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Combining Multiple Rows in a Column.

Bonnie described a common problem that occurs when importing a file into Excel. The file being imported is a scanned text file, and the import goes just fine, with one small glitch: in one column where there was wrapped text in the original document, the text now occupies several rows in the worksheet. Bonnie is looking for a way to combine those rows back into a single cell in that column.

There are a couple of ways this can be done. If you don't have to do this too often, a formulaic approach may be best. Just use the ampersand (&) to concatenate the contents of the rows you want to combine:

=C6 & " " & C7 & " " & C8 & " " & C9

The result is all the text combined into a single cell. You can copy this result to the Clipboard, and then use Paste Special to put it into the final cell where you need it. Finally you can delete the original multiple rows that are no longer needed.

If you need to perform this type of concatenation more than a few times, a simple macro may help:

Sub Combine()
    Dim J As Integer

    If Selection.Cells.Count > 1 Then
        For J = 2 To Selection.Cells.Count
            Selection.Cells(1).Value = _
              Selection.Cells(1).Value & " " & _
        Next J
    End If
End Sub

To use this macro, select the cells you want to concatenate and then run the macro. The contents of all the cells are combined into the first cell in the selection, then whatever is in the other cells is cleared. The macro doesn't delete any rows; that is left for you to do. It does, however, combine the contents quickly—even more quickly if you assign a shortcut key to the macro.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11496) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel here: Combining Multiple Rows in a Column.

Related Tips:

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 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:

Linda White    19 Dec 2014, 10:45
Would this work for multiple cells in a row?
Erik    19 Dec 2014, 10:00
Surendera, the use of & in Allen's formulaic approach is the shorthand version of the CONCATENATE function: omit the CONCATENATE function name and parentheses, and use the & symbol instead of the commas.
Surendera M. Bhanot    19 Dec 2014, 07:47
I am unable to get the desired result with "Fill > Justify" as suggested by Tony Shettle. It just does nothing the sort describe by him in Excel 2010.
Surendera M. Bhanot    19 Dec 2014, 07:43
This can be achieved with the CONCATENATE function This function joins up to 255 text strings into one text string. The joined items can be text, numbers, cell references, or a combination of those items. For example, if your worksheet contains a person's first name in cell A1 and the person's last name in cell B1, you can combine the two values in another cell by using the following formula:


The second argument in this example (" ") is a space character. You must specify any spaces or punctuation that you want to appear in the results as an argument that is enclosed in quotation marks.

CONCATENATE(text1, [text2], ...)

The CONCATENATE function syntax has the following arguments:

text1 Required. The first text item to be concatenated.
text2 ... Optional. Additional text items, up to a maximum of 255 items. The items must be separated by commas.
Bob Marotta    26 Mar 2012, 10:25
Just a thought......
Might want to add to macro -
need code to
CopyPasteSpecialValues for selection to convert all formulas to text so data can be used effectively.
Tony Shettle    25 Mar 2012, 17:03
In Excel 2010, you can achive a similar result very easily using the 'fill, justify' cmmand from the editing group on the home tab of the ribbon.
Widen the column that contains the data you want to concatenate so it is wide enough to hold the final text without wrapping. Highlight the cells you want to combine (e.g. A1 to A6) then select fill>justify. The contents of cells A1 to A6 will be concatenated into cell A1.

Note that this does not work with numbers, only text.
Give it a try!
(This tip is taken from Bob Umlas's book 'Excel outside the box')

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 © 2017 Sharon Parq Associates, Inc.