google sheetstext formatting
I am trying to find a way to split the numbers from a cell with the following data in it:
31(3)/22
With this data in a cell, in the adjacent 3 cells, I would like to see the results returned as thus
31 3 22
I have tried to use =iferror(arrayformula(split(D2:D,"(")),"") but obviously I have to do this 5 times which takes up multiple extra lines. I would then plan to fill the column to cover over 10,000 lines (possibly).
Any ideas? Is there a way of building multiple array formulae in one go?
Try this formula for cell H2:
=JOIN(", ", FILTER(B$1:E$1, B2:E2 = 0))
This filters the header row (B1:E1) on values from B2:E2 which has the value 0. The resulting strings are joined with ,.
You can copy this formula to the other cells in column H by dragging it across. The B2:E2 will adjust automatically to match the other rows.
Feel free to look at and copy the example spreadsheet I've set up.
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.
Best Answer
One can extract multiple parts of a string with regexextract, for example
expects each string to contain three groups of digits, separated by non-digits, and places these groups in separate columns. So,
31(3)/22becomes three entries: 33, 3, 22.Notation:
\da digit;\d+a group of 1+ digits;\Dsame for non-digits;()capturing parentheses: return the group of symbols within.A more forgiving form of the regex is
which allows some groups to be empty: e.g., it will also split 34(22) to 34 22.