Concatenate column dynamic cell contents

[TBP]

Hi,
Is there a formula I can use to concatenate a column with an unknown count of how many rows contain data.
examples

Heading1
bob
jane
rick

= bob jane rick

Heading1
bob

=bob

Thanks

 

[bewsh1987]

you could set your formula like the below there is no simple way unless you use code to do this

=TRIM(CONCATENATE(A1&" ",A2&" ",A3&" ",A4&" "))

 

[Joe4]

You can create your own function to do this in VBA. This should do it:

Function MyConcat(StartCell As Range) As String
'   Concatenates all entries in a column starting in starting cell referenced in formula


    Dim myLastCell As Range
    Dim cell As Range
    Dim myString As String
    
'   Find last cell with data in the selected column
    Set myLastCell = Cells(Rows.Count, StartCell.Column).End(xlUp)
    
'   Combine all non-blank entries
    For Each cell In Range(StartCell, myLastCell)
        If cell <> "" Then
            myString = myString & cell & " "
        End If
    Next cell
    
'   Remove last space
    MyConcat = Trim(myString)
    
End Function

So, if you wanted to concatenate all the entries in column A, starting on row 2, you would just then type this formula into your sheet:
=MyConcat(A2)

 

[bewsh1987]

You can create your own function to do this in VBA. This should do it:

Function MyConcat(StartCell As Range) As String
'   Concatenates all entries in a column starting in starting cell referenced in formula


    Dim myLastCell As Range
    Dim cell As Range
    Dim myString As String
    
'   Find last cell with data in the selected column
    Set myLastCell = Cells(Rows.Count, StartCell.Column).End(xlUp)
    
'   Combine all non-blank entries
    For Each cell In Range(StartCell, myLastCell)
        If cell <> "" Then
            myString = myString & cell & " "
        End If
    Next cell
    
'   Remove last space
    MyConcat = Trim(myString)
    
End Function

So, if you wanted to concatenate all the entries in column A, starting on row 2, you would just then type this formula into your sheet:
=MyConcat(A2)

that's cool

 

[TBP]

Joe4 awesome works great!
But can I run it from "PERSONAL.XLSB" when I do I get #NAME? error?
Thanks

 

[TBP]

you could set your formula like the below there is no simple way unless you use code to do this

=TRIM(CONCATENATE(A1&" ",A2&" ",A3&" ",A4&" "))

bewsh1987 I like the use of TRIM for a code free version.
Thanks

 

[TBP]

Joe4 awesome works great!
But can I run it from "PERSONAL.XLSB" when I do I get #NAME? error?
Thanks

Just found solution here had to change to =PERSONAL.XLSB!MyConcat(A2)
Thank you for the code.