# Combine cells in a column into one cell

This is a discussion on Combine cells in a column into one cell within the Excel Questions forums, part of the Question Forums category; I have 20 names in a column and want to combine all these names into one continuous string, in one ...

1. ## Combine cells in a column into one cell

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?

2. ## Re: Combine cells in a column into one cell

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!

3. ## Re: Combine cells in a column into one cell

What if I want to use a built in function? Is there one?

4. ## Re: Combine cells in a column into one cell

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

5. ## Re: Combine cells in a column into one cell

Works beautifully...What if I want to add a comma between names?

6. ## Re: Combine cells in a column into one cell

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```

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

8. ## Re: Combine cells in a column into one cell

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```

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•