How to Concatenate a Range of Cells in Excel
Free Excel VBA Function to Join Cells with a Delimiter
Sep 12, 2009
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:
Alternatively, the ampersand can be used to join the cells and the delimiter:
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 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:
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:
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:
Or, in simpler terms, it would return the identical result to using the concatenate function as follows:
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:
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:
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).
 |
