Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Space between text using Concatenate and Transpose function

0

Hi All,

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

Thanks and Regards,

Akash Sharma

Answer
Discuss

Answers

0
Selected Answer

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

=CONCATENATE(TRANSPOSE(B2:B12)&" "

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

    Application.Volatile
    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)
or
=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.

Discuss
1

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.

Discuss


Answer the Question

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