Combine Columns

[Boba Fetts]

I have a spreadsheet where I need to combine 9 columns. These columns contain words, not data. All 9 columns need to be combined into one column so I can upload to my CRM system. Some columns contain information, some do not and are left blank. I can combine the rows using CONCATENATE but here's the issue. I need the words separated by a comma and a space for readability. But, when I use the formula it adds a space and comma where there are cells that do not contain information. How do I write a formula so Excel just ignores the cells without data and only uses the cells with data? Right now the results look something like this:

 

[Fluff]

Hi & welcome to MrExcel
Have a look at the TEXTJOIN function.

 

[alansidman]

In addition to TEXTJOIN, you can combine the cells with Power Query setting the separator to what ever you wish, ie. a comma and a space.

 

[Boba Fetts]

Hi & welcome to MrExcel
Have a look at the TEXTJOIN function.

Better solution (easier) but still places phantom commas where there is not text to join. Is there a way for Excel to just skip cells without text and move on to the next without placing a comma?

 

[Fluff]

What was the formula you used?

 

[Boba Fetts]

What was the formula you used?

I used TEXTJOIN. Again the issue is getting this spreadsheet ready for upload to a CRM system. I want Excel to join the columns together but ignore empty cells all together. Most Rows have 3-4 cells with text. So only those cells should populate and be separated by a comma. Instead the formula also puts a space and comma for each empty cell. The result is messy.

 

[Fluff]

Yes, but what was the actual formula you used, not just the function?

 

[Boba Fetts]

Yes, but what was the actual formula you used, not just the function?

=TEXTJOIN(", ",TRUE,G2:O2)

 

[Fluff]

In that case I suspect that the "blank" cells are not actually blank, as that formula would ignore any blank cells.

do they contain formulae? if so what?

 

[Boba Fetts]

In that case I suspect that the "blank" cells are not actually blank, as that formula would ignore any blank cells

They're completely empty, no values, this was data entry. Thank you for your efforts.