Space between text using Concatenate and Transpose function


Hi All,

How to add space between each name using Concatenate and Transpose function. Excel attached for your reference.

Thanks and Regards,

Akash Sharma



Selected Answer

You can add a separator by appending it to your existing formula, like


If this is a one-off or occasional requirement that should be good enough. However, if the task has to be repeated often, incorporating the resulting array in a CONCATENATE formula seems too troublesome. The UDF below might seem more attractive.

Function ConcatRange(Target As Range, _
                     Optional ByVal Sep As String) As String
    ' 08 Mar 2019

    Dim Fun As String
    Dim Arr As Variant
    Dim V As Variant

    Arr = Target.Value
    For Each V In Arr
        If Len(Fun) Then Fun = Fun & Sep
        Fun = Fun & V
    Next V
    ConcatRange = Fun
End Function

Install the code in a standard code module and call it from the worksheet like you would any built-in function.

=ConcatRange($B2:$B12,", ")

The function can be copied vertically or horizontally, depending upon how you formulate the referenced range. Note that the range can be horizontal or vertical and may comprise more than one column. The separator is optional. In the first example above it is omitted. You can specify any separator of any length. The separator may even be the product of a function itself and change for each row.


Not sure of a direct way but maybe that's my lack of knowledge. The easiest way I can think of is to add a column with the formula =B2&" "  (i.e. build the extra space directly into the cell.) And then use concatenate and transpose on the new column.


Answer the Question

You must create an account to use the forum. Create an Account or Login