Concatenate multiple cells and remove unwanted commas between values (including cells with space separated values)

---

In my two previous blog posts, I demonstrated how to Concatenate multiple cells and remove unwanted commas between values using a formula and a macro respectively.
 
While this approach showed to be very effective, some viewers contacted me and stated that the aforementioned methods do not work properly if a cell in the data set contains more than one word. i.e. if there is a space in the cell, e.g. for words such as ”New York”.
 
If such words exist within the data set we will get an extra unwanted comma within the concatenation (e.g. “New, York”). Consider the data set below where I used the same formula from my last blog post. Take notice that some cells in this data set contain more than one word (e.g. Las Vegas, New York, New Jersey, North Carolina), and undesirably the formula is making these words comma delimited as well!!!

​I aim to please, so this week I will be providing the solution to this anomaly. Here is the magic formula that does the trick
 
=CONCATENATE(
       IF(COUNTBLANK(B2)=1,"",B2&CHAR(10)&","),
       IF(COUNTBLANK(C2)=1,"",C2&CHAR(10)&","),
       IF(COUNTBLANK(D2)=1,"",D2&CHAR(10)&","),
       IF(COUNTBLANK(E2)=1,"",E2&CHAR(10)&","),
       IF(COUNTBLANK(F2)=1,"",F2&CHAR(10))
)
 
Notice that I have used the COUNTBLANK() argument in the formula because unlike ISBLANK(), COUNTBLANK() considers spaces (i.e. “ “) as empty and returns 1. By applying the formula the words that are separated with space in each cell do not become comma delimited anymore.
  
Here is the final outcome when I apply the formula to the entire range: 

​Notice that cells with space separated values such as New York, New Jersey, North Carolina, and Nova Scotia have not been concatenated with a comma and this is the beauty of this formula :)
 
Note: the last argument (I.e. the last cell/column that is being concatenated) should not have the &”,” at the end of the argument, otherwise; we will end up getting an extra comma after the last word.

---