CODEWITHSUNDEEP
regexweb-scrapinggoogle-sheetsgoogle-sheets-formulaarray-formulas
tectomics
tectomics

Reputation: 147

Is it possible to IMPORTDATA in Google Sheets into one specific cell with line breaks rather than into new columns?

For example, I'm trying to do a REGEXEXTRACT from data on an imported page, but it's delimiting and going into hundreds of columns.

=REGEXEXTRACT(IMPORTDATA("https://url.js"),"id.*")

Upvotes: 3

Views: 692

Answers (2)

Peter F
Peter F

Reputation: 332

Use TEXTJOIN:

Combines the text from multiple strings and/or arrays, with a specifiable delimiter separating the different texts.

Example:

=REGEXEXTRACT(
TEXTJOIN(",", FALSE, IMPORTDATA("https://url.js")),
"id.*")

The first argument to TEXTJOIN is a delimiter. A comma probably caused Google Sheets to split the result into columns, so we will add the comma back in.

The second argument to TEXTJOIN is whether to ignore empty cells. I don't think it matters for this.

It looks this is not a perfect solution, for example quotation marks at the beginning of a column get removed.

Upvotes: 0

player0
player0

Reputation: 1

when using IMPORTDATA it's handy to wrap it into ARRAY_CONSTRAIN

=REGEXEXTRACT(ARRAY_CONSTRAIN(IMPORTDATA("https://url.js"), 500, 1), "id.*")

Upvotes: 1

Related Questions