An UDF for Concatenate Function

Question

Hello I was wondering whether it is possible to achieve this result using VBA/UDF?

https://ibb.co/2yn729f

Basically what I wanted to do is to be able to concatenate various string from different cell and achieve the shown result without typing the concatenate formula over and over but only one function with VBA/UDF, so we can get different kind of result based on the chosen cells

Answer 1

.

Sub Test()
Dim Source As Range
Dim i As Long
Dim Value

Set Source = Selection
If Source.Areas.Count < 1 Then
MsgBox "Select at min. 2 cells and the last cell as destination."
Exit Sub
End If
For i = 1 To Source.Areas.Count - 1
Value = Value & Source.Areas(i) & " "
Next
Source.Areas(Source.Areas.Count) = Left(Value, Len(Value) - 1)
End Sub

Answer 2

Hi,

try this vba code

select the desired cells and run the below vba macro

result in activesheet in cell C40

Sub Selection_Cells()

Dim rng As Range, r As Range, tgtCell As Range

Set rng = Selection

Set tgtCell = Range("C40") '<< result

Dim s As String

If MsgBox("NOTE!!! selected area is" & vbNewLine & rng.Address & " ??", vbOKCancel) = vbCancel Then Exit Sub

s = ""

For Each r In rng

If s = "" Then

s = r.Value

Else

s = s & " " & r.Value

End If

Next

tgtCell = s

End Sub

=====================

note

add a button and assign the macro