HEELPBOOK - How to Concatenate a Range of Cells (Excel 2007) #################################### 

Excel's concatenate function is useful for joining cells. However, it can be time consuming and laborious if joining numerous cells or when joining cells and a 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 method of these is perfectly acceptable and returns the required result. However, for a 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 string.

The range is the selection of cells to be joined together.
The string is an optional delimiter for if the user wishes to join the cells with a string in between (e.g. separate them with a comma).

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 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 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 period (full stop) to the end of the sentence. Running this function as it is would return the following 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).




############ ARTICLE INFO #############

Article Month: January
Article Date: 23/01/2013
Permalink: http://heelpbook.altervista.org/2013/how-to-concatenate-a-range-of-cells-excel-2007/
Source: http://suite101.com/article/how-to-concatenate-a-range-of-cells-in-excel-a147857
Language: English

View more articles on: http://www.heelpbook.net/
Follow us on Facebook: http://it-it.facebook.com/pages/HeelpBook/100790870008832
Follow us on Twitter: https://twitter.com/#!/HeelpBook
Follow us on RSS Feed: http://feeds.feedburner.com/Heelpbook
Follow us on Delicious: http://delicious.com/heelpbook
Linkedin: http://it.linkedin.com/pub/stefano-maggi/27/73a/b20
Google+ : https://plus.google.com/116990277568167008289/posts