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

Improve this question

1 Answer 1

Reset to default
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 ".

Improve this answer
1
  • 1
    This worked perfectly. Thank you very much for your informative and expedient response. Many internet cookies for you!
    – Aethroz
    Commented Oct 17, 2015 at 3:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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