google sheetsimportxml
Within Google Sheets, the function
=SPLIT(IMPORTXML("url here", "//title"),"|")
returns the following:
{"Linear Algebra", "School of Mathematics", "College Name", "City Name"}
which takes up 4 cells horizontally.
How do I get the first element of this array, "Linear Algebra"?
I don't want to use FIND() and LEFT(), because as far as I can tell it will take XML lookups.
Any help would be appreciated.
You can use a quite pragmatic approach to reach your goal.
=ARRAYFORMULA(
SPLIT( // string
REGEXREPLACE(
A1:A4, // text
"[users,<=>compani]", // regular_expression
"" // replacement
),
" ") // delimiter
)
copy/paste
=ARRAYFORMULA(SPLIT(REGEXREPLACE(A1:A4, "[users,<=>compani]",""), " "))
The regular_expression is cleaning up the string, in preparation for the SPLIT. The replacement is simply set to contain an empty string. The result, yields a string that contains one or two values, separated by a white space. The delimiter in the SPLIT is set accordingly and all is put into an ARRAYFORMULA.
A custom function will always receive either a rectangular array or a scalar value (number, string, boolean, or datetime) as each of its arguments. So the conversion from rectangular to jagged array has to happen inside the function. For example, by calling jagged(arr) on the input, where jagged is the following function:
function jagged(arr) {
var rowLength = arr.map(function(row) {
return row.reduce(function (a, b, i) {
return b === '' ? a : i+1;
}, 0);
});
return arr.map(function(row, i) {
return row.slice(0, rowLength[i]);
});
}
This function first computes the actual length of each row and then truncates the rows accordingly.
I emphasize that the call to jagged needs to happen from within the custom function that will use it, like so:
function myFunction (arr) {
jarr = jagged(arr);
// proceed to work with the jagged array
}
Putting =myFunction(jagged(A1:F3)) in a spreadsheet cell won't do it: the Sheets will automatically inflate the return values of jagged to a rectangular array before passing them to myFunction.
Best Answer
Answered my own question with a little more googling: https://stackoverflow.com/questions/32307788/get-first-item-from-split
=INDEX(SPLIT(IMPORTXML("url here", "//title"),"|"),1,1)works.