google sheets
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
The following little piece of code gives you the result, without prior knowledge about the character code number.
function mySplit(range, delimiter) {
var output = [], maxCol = range[0].length;
for(var i=0, iLen=range.length; i<iLen; i++) {
var colCounter=0, counter=0;
for(var j=0, jLen=range[0].length; j<jLen; j++) {
if(range[i][j] == "") {
colCounter++;
} else {
if(range[i][j].split(delimiter).length > 1) {
counter++;
}
}
}
output.push(counter + "/" + (maxCol - colCounter));
}
return output;
}
I've created an example file for you: Certain Text Characters
If you copy the file, then you can access the code. Add the code into your existing sheet by going to Tools\Script editor in the main menu. Paste the code and save.
Best Answer
This would work:
The operator
&is concatenation; its effect is identical toconcatenateformula, but I find&easier to type.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".