i would like to make a custom VBA formula that concatenates selected cells by pu

Question

i would like to make a custom VBA formula that concatenates selected cells by putting between them a custom sign.

Example:

i have these three cells : A1="cell 1", A2="cell 2" and A3="cell 3"

i want this result : "cell 1 - cell 2 - cell 3"

so i want to add this " - " between the both of them

to get the same result with the concatenate function i have to write like this: =CONCATENATE(A1," - ",A2," - ",A3)

to avoid having to select every cell one by one and add the separating sign manually i would like to program smth. like this:

=putTogether(A1:A3," - ")

please reply to me if you have any idea

thanks in advance :)).

Explanation / Answer

Try this. ALT+F11 to open VB editor, right click 'ThisWorkbook' and insert module and paste the code in on the right. Close VB editor.

On the worksheet call with this and it will use your default dlimiter

=putTogether(A1:A3)

or you can specify a different delimiter if you call with say this

=putTogether(A1:A3," , ")

Function putTogether(rng As Range, Optional delim As String) As String
Dim c As Range
If delim = "" Then delim = " - "
For Each c In rng
If c.Value <> "" Then
    putTogether = putTogether & c.Value & delim
End If
Next
putTogether = Left(putTogether, Len(putTogether) - Len(delim))
End Function

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.