google sheets
I have Google Sheets data that looks like this:
A | B | C
-----------
1 | a | |
2 | | b |
3 | c | |
4 | | | d
5 | | e |
6 | f | |
7 | | | g
8 | | h |
I need to combine this data into one column like this:
| D |
-----
a
b
c
d
e
f
g
h
For every row, there is only one data cell in Columns A, B, or C. Is it possible to do this using a Google Sheets formula?
Thank you all for your help.
Use a custom function.
function result(range) {
var splitCol = 1; // split on column B
var output2 = [];
for(var i=0, iLen=range.length; i<iLen; i++) {
var s = range[i][splitCol].split("\n");
for(var j=0, jLen=s.length; j<jLen; j++) {
var output1 = [];
for(var k=0, kLen=range[0].length; k<kLen; k++) {
if(k == splitCol) {
output1.push(s[j]);
} else {
output1.push(range[i][k]);
}
}
output2.push(output1);
}
}
return output2;
}
The script evaluates each row, and particularly the second column of each row (in JavaScript arrays are zero based, so column 2 corresponds to index 1 of the array). It splits the contents of that cell, into multiple values and uses the "\n" as delimiter (line feed). After that it adds the existing info to an array and only add the individual results, when it hits index 1 (k == 1). The newly prepared row is then added to another array, that's being returned to show the result.
data
result
I've created an example file for you: multi-line cells into new rows.
Provided that last column is populated, then something like:
=HLOOKUP(1E+100,B1:Z1,1)
assuming a row starts in ColumnB and ends in ColumnZ.
Best Answer
Data type : strings
use some concatenation. Try this in
D1Data type : value