WISDOMBYDATA
  • BLOG
    • Blog Guide
    • Blog History
  • EXCEL
    • Functions & Formulas
    • VBA & Macros
    • VLOOKUP
    • Pivot Tables
    • Conditional Formatting
    • Tricks & Shortcuts
  • BI
    • SAP BOBJ/BW
    • Tableau
  • SQL
  • ABOUT
    • About WBD
    • About Me

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

9/25/2018

0 Comments

 

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!!!
Picture

​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: 
Picture

​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.


Picture

0 Comments



Leave a Reply.

    Categories

    All
    BI
    EXCEL
    MISC
    SQL

    Archives

    June 2020
    May 2020
    April 2020
    March 2020
    February 2020
    December 2019
    November 2019
    October 2019
    September 2019
    August 2019
    July 2019
    June 2019
    May 2019
    April 2019
    March 2019
    February 2019
    January 2019
    December 2018
    November 2018
    October 2018
    September 2018
    August 2018
    July 2018
    June 2018
    May 2018
    April 2018
    March 2018
    September 2017
    August 2017
    July 2017
    June 2017
    May 2017
    April 2017
    March 2017
    February 2017
    January 2017
    December 2016
    November 2016
    October 2016
    September 2016
    August 2016
    July 2016
    June 2016
    May 2016
    April 2016
    March 2016
    February 2016
    May 2015
    April 2015
    March 2015
    February 2015
    January 2015
    December 2014
    November 2014
    October 2014
    September 2014
    August 2014
    April 2014
    March 2014
    February 2014
    January 2014
    December 2013
    November 2013

Powered by Create your own unique website with customizable templates.
  • BLOG
    • Blog Guide
    • Blog History
  • EXCEL
    • Functions & Formulas
    • VBA & Macros
    • VLOOKUP
    • Pivot Tables
    • Conditional Formatting
    • Tricks & Shortcuts
  • BI
    • SAP BOBJ/BW
    • Tableau
  • SQL
  • ABOUT
    • About WBD
    • About Me