Google Sheets – Using IMPORTXML with Line Breaks

formulasgoogle sheetsgoogle-sheets-arrayformulaimportxmlregex

I am using ImportXML to get some data from a website which looks like the following:

=ImportXML("https://www.papercall.io/events?cfps-scope=&keywords=uk","//div[@class='panel panel-default']")

While it gets the data ok, two of the fields are both headings <h4> and the sheet seems to be importing both of these together into one field. Is there a way I can change the formula above to either change the <h4> into a <p> or just to add a line break after the <h4> is closed?

Best Answer

  • you can regex it like this:

=ARRAYFORMULA({INDEX(IMPORTXML(
 "https://www.papercall.io/events?cfps-scope=&keywords=uk", 
 "//div[@class='panel panel-default']"),,1), 
 IFERROR(REGEXEXTRACT(INDEX(IMPORTXML(
 "https://www.papercall.io/events?cfps-scope=&keywords=uk",
 "//div[@class='panel panel-default']"),,2), "(.*)Event Dates")), 
 IFERROR(REGEXEXTRACT(INDEX(IMPORTXML(
 "https://www.papercall.io/events?cfps-scope=&keywords=uk",
 "//div[@class='panel panel-default']"),,2), "(Event Dates.*)"))})

enter image description here