Hi All,
How to add space between each name using Concatenate and Transpose function. Excel attached for your reference.
Thanks and Regards,
Akash Sharma
Hi All,
How to add space between each name using Concatenate and Transpose function. Excel attached for your reference.
Thanks and Regards,
Akash Sharma
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.
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.