How to Concatenate a Range of Cells (Excel 2007)

Excel's concatenate [gs function] is useful for joining cells. However, it can be time consuming and laborious if joining numerous cells or when joining cells and a [gs delimiter].

To combine four cells (A1, B1, C1 and D1) containing address fields into one field, separated by commas, one possible option would be to concatenate these as follows:

=CONCATENATE(A1,", ",B1,", ",C1,", ",D1)

Alternatively, the ampersand can be used to join the cells and the delimiter:

=A1&", "&B1&", "&C1&", "&D1

Either [gs method] of these is perfectly acceptable and returns the required result. However, for a [gs user] wishing to join many cells it would result in a very long formula.

A much more efficient method would be to create a function that essentially concatenates a range of cells but with the advantage of also accepting an optional delimiter which can be used to create a delimited list.

Example VBA Function

Function MAKELIST (ByVal cellRange As Range, Optional ByVal delimiter As String)


'Function to join a range of cells together with an optional


Dim c As Range


Dim newText As String


Dim Count As Integer


Count = 0  


newText = ""


For Each c In cellRange


Count = Count + 1


newText = newText & c.Value


If Count < cellRange.Count


Then


newText = newText & delimiter


End If


Next


MAKELIST = newText


End Function

Function Explanation

The function accepts two input parameters, a range and a [gs string].

The count variable is used to check whether the current cell is the last one in the range, the delimiter is only to be used in between text items so will not be appended to the end of the last cell.

In the previous example this [gs function] would be called as follows:

=MAKELIST(A1:D1,", ")

Joining Cells on Multiple Rows

It's worth noting that if a range goes over one row, Excel works through each row in turn. Using the following example:

=MAKELIST(A1:D2,", ")

Excel would join cells A to D on row 1 followed by cells A to D on row 2. Essentially this could also be achieved using the following [gs method]:

=MAKELIST(A1:D1,", ") & ", " & MAKELIST(A2:D2,", ")

Or, in simpler terms, it would return the identical result to using the concatenate function as follows:

=CONCATENATE(A1,", ",B1,", ",C1,", ",D1,", ",A2,", ",B2,", ",C2,", ",D2)

Making a Sentence and Converting Text to Sentence Case

To combine multiple cells containing individual words into a sentence the delimiter would be a space.

In the following example imagine the cells of the first row contain text as follows:

A1 = This


B1 = Is


C1 = A


D1 = Sentence

To combine these cells into a full sentence the function would be used in a formula as follows:

=MAKELIST(A1:D1, " ")&"."

The last part of this function appends a [gs period] (full stop) to the end of the sentence. Running this function as it is would return the following [gs string]:

"This Is A Sentence."

The text itself is fine but the words shouldn't start with capital letters. To correct this, a function to convert text into sentence case is required. Once the sentence case function is inserted into the VBA module, the two functions can be combined as follows:

=SENTENCE(MAKELIST(A1:D1," ")&".")

Function Limitations

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

SOURCE	LINK	LANGUAGE	ENGLISH