Concatenating a range of cells in EXCEL

I often use Excel to create SQL UNION statements and update statements by harnessing Excel's concatenate function. Typically, I’m creating UNION statements. I list the column names from the tables I’m trying to union and then use Excel until I get them lined up and mapped correctly. My final step is to use the CONCATENATE function to append the necessary comma between the column names.

Recently, I created a view that combined the pay code, deduction code, and benefit code tables. I later used that view to create a SmartList object so that all of those different codes could be viewed in one place. You can download the SmartList on GPUG's 'share my code' site. Or e-mail me for it (leslievail@earthlink.net). I used Excel to build the UNIONs and the CONCANTENATE function to insert the commas. While using CONCANTENATE accomplished my objective, it was a bit time consuming than I had hoped. There has to be a better way, I thought. And there is.

The CREATE_DELIMITED function. Using a function to join a range of cells together separated by a certain character (or characters) is much easier than using the CONCATENATE function. I found an article on the Internet the other day that described such a function. It works magnificently. If you use Excel to create SQL statements as I have described, I recommend that you copy this function into your Personal Macro Workbook. By creating the function in your macro workbook, you’ll be able to use it in all of your workbooks. Katie Giles published this function on her blog on September 12, 2009. I modified it slightly to fit my needs, but the logic was created by Katie Giles.

The following text will show you how to create and call the function, how it works, and its limitations.

Concatenating the old fashioned way To combine four cells (A1, B1, C1 and D1) into one field, separated by commas, you could use the following two options: 1. Hard code the concatenation using the CONCATENANTE function and include the literal string between each cell: =CONCATENATE (A1,", ",B1,", ",C1,", ",D1) 2. Alternatively, you can use the ampersand to join the cells together =A1&", "&B1&", "&C1&", "&D1 Both of the options above will return the desired output, but if you need to join many cells together, the formula could become quite long and hard to manage.

Using a function to concatenate cells Having a function that will accept an optional parameter for the delimiter is much more efficient than creating the formula by hand. The VBA code below creates the function I now use to create my unions. Example VBA Function

Function CREATE_DELIMITED (ByVal cellRange As Range, Optional ByVal delimiter As String)
Dim c AS Range
Dim DataStatement AS String
Dim Count AS Integer
Count = 0
DataStatement = ""
For Each c in cellRange
Count = Count + 1
DataStatement = DataStatement & c.Value
If Count < cellRange. Count Then
DataStatement = DataStatement & delimiter
End If
Next
CREATE_DELIMITED = DataStatement
End Function
 
(thanks to Perpetual Acumatica Learner for correcting one of the lines of this code)

How the CREATE_DELIMITED function works To call the function, you would use the following syntax: =CREATE_DELIMITED (range [, string]) The function accepts two input parameters, a range and a string. · range is the selection of cells you want to join together · string is the character(s) you want to use for the delimiter. This is an optional parameter. The function uses a count variable to check whether the current cell is the last one in the range. You need to know when you are on the last sell so that you don't append the delimiter to the end of the statement.

Joining a range of cells with the CREATE_DELIMITED function Using the CREATE_DELIMITED function to create the concatenated result would look like this: =CREATE_DELIMITED (A1:D1,", ") The range is A1:D1 and the string delimiter is a comma and space.

Joining cells from multiple rows If the range you want to join includes more than one row, Excel works through each row in turn. For example, if your function call resembled the following: =CREATE_DELIMITED (A1:D2,", ") Excel would join cells A to D on row 1 followed by cells A to D on row 2. You could also achieve this by using two function calls and concatenating them together. The formula would look like this: =CREATE_DELIMITED (A1:D1,", ") & ", " & CREATE_DELIMITED (A2:D2,", ") If you were to use the built-in CONCATENATE function, the formula would look like this: =CONCATENATE(A1,", ",B1,", ",C1,", ",D1,", ",A2,", ",B2,", ",C2,", ",D2) The formula using the CREATE_DELIMITED function is by far the easiest one to use.

CREATE_DELIMITED Function Limitations The CREATE_DELIMITED function is only suitable for joining a range of adjacent cells. This function cannot be used to join cells in different locations on a spreadsheet or to join text strings entered directly into the formula (rather than being entered into the cells themselves).

Until next post!

Leslie