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:
But you want to combine the values on each row like so:
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.
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).
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.
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.)
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.
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.
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.