VBA function to Concatenate

In this post, let's take a look at VBA code/function to concatenate string. Like in Microsoft Excel concatenate function you cant provide function with range. So you have to provide function with each cell at a time which is time consuming.  The best solution to help

VBA Code

Function VBAConcatenate(target As Range, delimiter As String) As String
Dim str As String

For Each c In target
    str = str + delimiter + c
Next c

If delimiter = "" Then
    VBAConcatenate = str
Else
    VBAConcatenate = Right(str, Len(str) - 1)
End If

End Function

VBAConcatenate require following parameters

Target: Target is range of cell you wish to concatenate.

Delimiter: Delimiter is any separator you want to use between concatenated text.

Example 1: You have A, B, C and D in range C6:C9 and you want the results as ABCD than you will use VBAConcate in following manner.

Formula: =VBAConcatenate(C6:C9,"")  will return ABC

Click on image below to view enlarge

Example 2: You have A, B, C , D and E in range A6:E6 and you want the results as A|B|C|D|E  than you will use VBAConcate in following manner.

Formula: =VBAConcatenate(A6:E6,"|" )  will return A|B|C|D|E

Click on image below to view enlarge

Note this is very useful when you want to type a SQL query. In a query you need field name separated by comma. I use this function to get me list of field names in query.

You can also download the Microsoft Excel file with VBAconcatenate examples here. Just click on link below, to view code press Alt + F11

Click here to Download

The only issue with user define functions like VBAConcatenate is they remain limited to that MS Excel workbook where they are written. To use then in all workbooks on your system, you will have to convert this function into add-ins. So, once you install them on your computer you can use VBAConcatenate than in any workbook. You can download VBAConcatenate add-in here.

Click here to download Add-ins

If you need help with installing add-ins, please visit my earlier post How to install Add-ins.

Thanks for spending time in reading post. Request you to leave your comments. Also, you can receive updates, add-ins and e-books, its free.

We assure you knowledge, not SPAM!