Combining cells unless a cell contains certain text

Question

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"?

You can use `TEXTJOIN()` if you have `Excel2019` or `Excel365`. — Harun24hr, May 28 '21 at 07:52
You can check every single one with IF combined with COUNTIF if they contain the text `Nothing else carby` using wildcards `*`. If the count is 0, return cell & ",". IF count is not 0, then return nothing "". And then combine all cells into one. Indeed a long formula. — Foxfire And Burns And Burns, May 28 '21 at 08:08
Thank you @Foxfire And Burns And Burns - I do have Excel 2019, how would that formula be written out please? — rnmuk, May 28 '21 at 08:20
If you have Excel2019, then try TEXTJOIN. I do not have it here so can't help. But to check a single cell, if would be something like `IF(COUNTIF(C13;"*"&"Nothing else carby"&"*")=0;C13&", ";"")` — Foxfire And Burns And Burns, May 28 '21 at 08:26

JvdV · Answer 1 · 2021-05-28T08:28:23.767

1

You may, in Excel 2019, use:

=TEXTJOIN(", ",,IF(C13:C19<>"Nothing else carby",C13:C19,""))

If "Nothing else carby" can be a substring inside a cell's value, try:

=TEXTJOIN(", ",,IF(ISNUMBER(FIND("Nothing else carby",C13:C19)),"",C13:C19))

Confirm through CtrlShiftEnter

edited May 28 '21 at 08:28

answered May 28 '21 at 08:25

JvdV

70,606
8
39
70

1

Nice one!!! Did not though about using FIND and ISNUMBER. Upvoted – Foxfire And Burns And Burns May 28 '21 at 08:29

score 0 · Accepted Answer · answered May 28 '21 at 08:32

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

In case you find my answer useful, you might upvote it (not accept, you can only accept one answer). — Dominique, May 31 '21 at 08:28

Combining cells unless a cell contains certain text

2 Answers2