Combine cells in a column into one cell

ravigv

New Member
Joined
May 27, 2002
Messages
20
I have 20 names in a column and want to combine all these names into one continuous string, in one cell. So whenever one of these cells changes, the output in the single cell should automatically change.

I dont want to concatenate them individually as there maybe times when i have more than 20 names and I'll need to manually change the formula in that case.

Any ideas?
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
We can write a custom function to do that. Here is one:

Code:
Function CombineColumn(myRange As Range)

    Dim cell As Range
    For Each cell In myRange
        If Len(cell) > 0 Then
            CombineColumn = CombineColumn & cell.Value
        End If
    Next cell
    
End Function

For example, let's say that in cell A1 we wanted to combine the cells from range B1 to Z1. Then, simply enter this formula in A1:
=CombineColumn(B1:Z1)

If you do not know what row the data will end in, simply pick some range that you know the data will never exceed, i.e. B1:IV1


Edit: Sorry, I originally had it set up for rows, not columns, in the example, though the same function will work!
 
Upvote 0
Not one that will do what you want. It may be possible to write a complicated formula to do the same thing, but I have no idea how to do it.

When you write your own custom function, it works just the same as a "built-in function" (on that particular worksheet).
 
Upvote 0
Here is a version that will put commas between names:

Code:
Function CombineColumn(myRange As Range)

    Dim cell As Range
    For Each cell In myRange
        If Len(cell) > 0 Then
            CombineColumn = CombineColumn & cell.Value & ","
        End If
    Next cell
    CombineColumn = Left(CombineColumn, Len(CombineColumn) - 1)
    
End Function
 
Upvote 0
I found this thread and stole your wonderful bit of code jmiskey. Unfortunately I am a dunce when it comes to VB or VBA and I have a small problem. When there are no values in the column being combined I get a result of #VALUE!
Is there anything that can go in the code which will eliminate the error result? Your help would be much appreciated. Thanks.
 
Upvote 0
WELCOME TO THE BOARD, shockeymoe

Here is a variation of the code which will handle the blanks without errors:

Code:
Function CombineColumn(myRange As Range)

    Dim cell As Range
    For Each cell In myRange
        If Len(cell) > 0 Then
            CombineColumn = CombineColumn & cell.Value & ","
        End If
    Next cell
    
    If Len(CombineColumn) > 0 Then
        CombineColumn = Left(CombineColumn, Len(CombineColumn) - 1)
    Else
        CombineColumn = ""
    End If
    
End Function
 
Upvote 0
That's great man, thanks!
I've found all kinds of answers here and some really interesting solutions just by searching the discussions!
Powerful stuff that really helps because I'm flying by the seat of my pants and I don't use Excel enough to warrant taking VBA courses. Thanks again.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top