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]

If they were totally empty you would get something like

+Fluff.xlsm
	G	H	I	J	K	L	M	N	O	P
2	SK055741	Derbyshire		Corbar		E05010629	England	E10000007		SK055741, Derbyshire, Corbar, E05010629, England, E10000007
Master

Cell Formulas
Range	Formula
P2	P2	=TEXTJOIN(", ",TRUE,G2:O2)

in an unused cell put =ISBLANK(I2) (change I2 to reflect one of your blank cells). What does it return?

 

[alansidman]

Just an alternative
I took the following data and loaded to Power Query

Book1
	A	B	C	D	E	F	G
1	Column1	Column2	Column3	Column4	Column5	Column6	Column7
2	AB	CD		EF	G		H
3	FF		GG		HH	I
Sheet1

ran the following Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"},Combiner.CombineTextByDelimiter(", ", QuoteStyle.None),"Merged"),
    #"Replaced Value" = Table.ReplaceValue(#"Merged Columns",", ,",", ",Replacer.ReplaceText,{"Merged"})
in
    #"Replaced Value"

and the result looks like the following

Book1
	A
1	Merged
2	AB, CD, EF, G, H
3	FF, GG, HH, I,
Sheet3