4

I would like to combine text from Cell A2, B2, C2-I2, J2, into a sentence in E2.
Same thing would continue down in Row 3, 4, 5, etc.
I would want the sentence to end up being "[A2] can be bought from [B2], by [C2-I2] after you do the quest: "[J2]"."
C2-I2 each cell may or may not have a name. If it has a name, it should be included with a comma. If not, it should be left out. I can add the comma in-cell if it makes it easier.

Here is an example Example pic

4

This would work:

=A2&" can be bought from "&B2&" by: "&join(", ",filter(C2:I2,len(C2:I2)))&", after completing the quest """&J2&""""

The operator & is concatenation; its effect is identical to concatenate formula, but I find & easier to type.

join(", ",filter(C2:I2,len(C2:I2)))

takes only the nonempty cells in the range C2:I2 and joins them, separating by comma and space. I am assuming at least one cell in this range is nonempty, otherwise the sentence won't make much sense no matter what.

The contraption ..."""&J2&"""" at the end is escaping quote marks within a string. To be escaped, " must be repeated. So, """" is the string containing one character, namely ".

  • 1
    This worked perfectly. Thank you very much for your informative and expedient response. Many internet cookies for you! – Aethroz Oct 17 '15 at 3:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.