I have an Excel spreadsheet with 7 cells (in a column) with data in, these are C13 to C19 I have a formula that combines all the data in these cells together and puts it into one cell, this formula is =C13&", "&C14&", "&C15&", "&C16&", "&C17&", "&C18&", "&C19 and works fine. However, can I alter this formula to miss out on any cell that contains the text "Nothing else carby"?
The formula should not be that long, as you can see:
=TEXTJOIN(",",TRUE,IF(C13:C19="Nothing","", C13:C19))
(I just used "Nothing" instead of the whole word for readability reasons.)
Explanation of the parameters:
"," : delimiter: between every cell content, a comma is added.
TRUE : treatment of blank cells (don't put them).
IF (...) : In case cell content is "Nothing", put an empty string.
In combination with the previous parameter,
just one comma will be put in the result:
"a,b,c,e,f,g" and not "a,b,c,,e,f,g" (see result).
Used data:
a
b
c
Nothing
e
f
g
Result:
a,b,c,e,f,g