Results 1 to 9 of 9

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. #1
    New Member
    Join Date
    May 2002
    Posts
    20

    Default 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. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,522

    Default 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!
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    May 2002
    Posts
    20

    Default Re: Combine cells in a column into one cell

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

  4. #4
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,522

    Default 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).
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    May 2002
    Posts
    20

    Default Re: Combine cells in a column into one cell

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

  6. #6
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,522

    Default 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
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    New Member
    Join Date
    Nov 2003
    Location
    Winnipeg, Manitoba
    Posts
    6

    Default

    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. #8
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,522

    Default 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
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  9. #9
    New Member
    Join Date
    Nov 2003
    Location
    Winnipeg, Manitoba
    Posts
    6

    Default

    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.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com