Excel Function – AllConcat | Combine all cells value

You want to combine all cells value scattered in rows and columns. But you dont want to select each cell seperate by comma, then you are at right place.

We have designed a function for you which can combine / concat all value by just selecting range inside the function.

=AllConcat(A2:D7)

Public Function AllConcat(a As Range) As String

Dim b As String
Dim aa As Range

    For Each aa In a
    
        b = b & " " & aa.Text
    
    Next aa

AllConcat = WorksheetFunction.Trim(b)

End Function

Copy above function inside the VBA editor (Alt+F11) >> Insert module >> Copy above code. Its done.

You will get Output in Single Cell.

Use case –

• If you want to align data in table format, which is scattered in different cells. (Example, Name and Address)
• If you converted PDF File to excel and Text string get divided into columns. To combine such text.

Lets try and Enjoy!