Combine Excel Cells into One

In this post I offer a tiny VBA macro snippet I once wrote to save me a lot of time preparing spreadsheets for my business partner. It’s trivial for a VBA programmer but if you’re not so comfortable with VBA macros, just use this code to address the same problem or even adapt it for any other situation requiring a similar approach.

Concatenate All Contiguous Cells to the Right into One Cell

Say you have some cells like this:

Values split across a number of cells
Values split across a number of cells

But you want to combine the values on each row like so:

All cells to the left combined into the rightmost cell
All cells to the left combined into the leftmost cell

In other words, the values to be concatenated are spread horizontally across any number of contiguous cells. The “target” cells to hold the combined values are in one column (column A in the screenshot above) along contiguous rows.

Of course, there is more than one way to solve this. I’ll mention an alternative later but for now here is a macro that does the job.

Sub Combiner()
    Dim c As Long, r As Long

    Do
        c = 1
        Do While Not IsEmpty(ActiveCell.Offset(0, c))
            ActiveCell = ActiveCell & " " & ActiveCell.Offset(0, c)
            c = c + 1
        Loop

        ActiveSheet.Cells(ActiveCell.Row + 1, 1).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, 0))

End Sub

Before running the macro, make sure the first target cell is currently selected. In the first screenshot, that’s A1.

Note that the macro doesn’t actually delete the contents of the “non-target” cells. You can either do that manually or adjust the macro to do it for you. (If you’re not sure how to do that, please leave a comment and I’ll update this post with the appropriate edit.)

That’s all there is to it. Keep reading if you’re new to macros or are interested in possible alternatives.

Assumptions

To work properly, make sure the values to be combined are in contiguous cells and in contiguous rows. In the screenshot above, if there were a value in A5, it would be ignored by the macro. If there were a value in F1, that would also be ignored (assuming that E1 is blank).

If you can imagine the movement of manual typewriters laterally across and down a sheet of paper, in a “Z” pattern, then that’s the same sort of movement the macro follows.

You can modify and adapt the macro for any other operation where you have a bunch of cells on a lot of rows to process in a similar fashion (i.e. across-across-down, across-across-down, etc).

Primer

Because the solution is a macro, you’ll need to know how to create and run macros. I don’t intend to give a detailed introductory lesson here on macros but below are some pointers in case this is all new to you.

Macros are accessed via the Developer tab on the Office ribbon. (See screenshot below.) The Developer tab is not enabled by default. If you cannot see it, enable it by following these instructions.

The Developer tab on the Office ribbon
The Developer tab on the Office ribbon

To create the macro, click the Macros button on the Developer tab. A dialog box will pop up. In this dialog, enter the desired macro name (I’ve called it “Combiner” in my example) and click “Create”. (See screenshot below.)

Dialog box for creating a new macro
Values split across a number of cells

This should open the VBA editor with an empty Sub called “Combiner” ready for you to fill in. Edit this sub so that it looks exactly like the code provided above.

This way that I just described is much simpler than the explanation in this article but I’ll offer the link anyway for further reading plus tips for speeding up macro execution.

Alternatives

The CONCATENATE Function

If you don’t have a lot of rows to combine, you can use the CONCATENATE function. Here is an article that shows how that function can be used. The values in that article are split across rows, not columns, so you’ll do it slightly differently but the important thing is to know how the CONCATENATE and F9 key together can work.

The sep= Statement for Non-Comma Delimited CSV Files

Originally, I used this macro to “fix” CSV files that Excel was incorrectly splitting on commas it found in the data. By default, Excel treats commas as the field delimiter so, if your CSV data contains commas, Excel will split the string into a new cell at each comma. If the field delimiter is actually a different character, your data is split incorrectly.

I originally used my macro to recombine all the cells before using the Text to Columns feature to split on the real field delimiter. That is, until I discovered that I could specify the field delimiter by inserting sep=field_delimiter_character as the first line of the CSV file. The second answer in this post explains how to use the sep= statement to split on non-comma delimiters.

I might have overcomplicated the task by writing so much on it. However, if it is helpful, it will have been worth it!

UPDATE: 13 JANUARY 2015
One caveat about the macro: if you have many columns and/or rows to process (e.g. tens or hundreds of thousands of cells), the macro may be optimized by not updating ActiveCell once for every lateral cell it encounters. Instead, try updating ActiveCell only once per row. I can imagine a couple of ways to do this (e.g. slot the cell values into an array of strings and then concatenate them using the Join function; preallocate a fixed length temporary string variable and insert cell values into the appropriate offsets within this string variable. Whatever the approach, I would prefer to avoid concatenating to a string variable cell-by-cell because VBA is notorious for being slow when concatenating to variable length strings in a loop.

Again, if you run into this issue and need more info, please leave a comment and I’ll respond with some suggestions or even some new code.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s