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.

Combining Multiple Rows in a Column

by Allen Wyatt
(last updated July 18, 2016)

6

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 & " " & _
              Selection.Cells(J).Value
            Selection.Cells(J).Clear
        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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Pulling Formulas from a Worksheet

The formulas in your worksheet can be displayed (instead of formula results) by a simple configuration change. You can then ...

Discover More

Calculating Weekend Dates

Do you look forward to the weekend? Well, you can use Excel to let you know when the next weekend begins. Here's how you can ...

Discover More

Adding a Toolbar to the Taskbar

Normally the taskbar is used to display icons for currently running programs on your system. Here's how you can go beyond ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Using the Fill Handle from the Keyboard

The fill handle can save a huge amount of time when you are editing a worksheet. If you are really good at using the keyboard ...

Discover More

Setting a Length Limit on Cells

Limiting what can be entered in a cell can be an important part of developing a worksheet that other people use. Here are a ...

Discover More

Capitalizing Just a Surname

Changing the capitalization of text is, believe it or not, a common task in Excel. Common or not, it can be frustrating to ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 9 + 4?

2014-12-19 10:45:47

Linda White

Would this work for multiple cells in a row?


2014-12-19 10:00:08

Erik

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.


2014-12-19 07:47:22

Surendera M. Bhanot

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.


2014-12-19 07:43:59

Surendera M. Bhanot

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:

=CONCATENATE(A1," ",B1)

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.

Syntax
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.


2012-03-26 10:25:54

Bob Marotta

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.


2012-03-25 17:03:23

Tony Shettle

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')


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.