Concatenate cells and remove unwanted commas between values in MS Excel (Using a Macro)

---

In my previous blog post I demonstrated how to concatenate cells and remove extra values between cells using a formula. i.e using the following formula.

=SUBSTITUTE(TRIM(CONCATENATE(B2," ",C2," ",D2," ",E2," ",F2))," ",",")

Seeing that the formula above may become too long if a higher number of cells are being concatenated, to keep the sanity, this week I want to show how to make this formula more efficient using a macro. Consider the data set below: 

The code below will create a function (i.e. CONCATCELLS) that when used in Excel will concatenate a range with a delimiter without needing to input the reference to all cells within the formula.

Function CONCATCELLS(REF As Range, DELIMITER As String) As String
Dim CELL As Range
Dim CONCAT As String
For Each CELL In REF
     CONCAT = CONCAT & CELL.Value & DELIMITER
     Next CELL
CONCATCELLS = Left(CONCAT, Len(CONCAT) - 1)
End Function
 
 Enter the code above under Module within the Developer tab of the Ribbon and then Save the spreadsheet .
The macro will create the following function:

= CONCATCELLS(B2:F2," ")

Here is what the function above returns for the B2:F2 range of our dataset.

Now all we need to do is to replace the CONCATENATE part of the original formula with our new CONCATCELLS function as follows:

=SUBSTITUTE(TRIM(CONCATCELLS (B2:F2," ")), " ", ",")
 
Here is the final outcome:

As you could see the formula is now shorter and there is no need to reference all cells :)
​
I have enclosed the sample workbook below so it could be referenced if needed.

concatcells.xlsm
File Size:	21 kb
File Type:	xlsm

Download File

---

---