Extract Substrings using importxml in google sheets

Question

Using IMPORTXML in google sheets. I want to extract part of the result into one cell.

=IMPORTXML(B1,"//div[@class='orca-rating SwtJyda color-yellow tbody-6']/span")

I got the result spread over several columns. B8:F8

The inspect element is like this. I only want the value "2". It is in cell B8.

I think this can be done using substring-after. But I could not get the correct result.

Although I'm not sure whether this is included in your expected direction, I proposed an answer. Could you please confirm it? If I misunderstood your question and that was not useful, I apologize. — Tanaike
– Tanaike, Commented Jan 3, 2023 at 8:33

Tanaike · Accepted Answer · 2023-01-03 08:32:29Z

3

In your situation, how about the following samples?

=REGEXREPLACE(JOIN("",IMPORTXML(B1,"//div[@class='orca-rating SwtJyda color-yellow tbody-6']/span")),"[^0-9]","")

=REGEXEXTRACT(JOIN("",IMPORTXML(B1,"//div[@class='orca-rating SwtJyda color-yellow tbody-6']/span")),"\((.*)\)")

References:

answered Jan 3, 2023 at 8:32

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TipVisor · Accepted Answer · 2023-01-03 09:27:31Z

1

I use this formula. That works too.

=INDEX(  IMPORTXML(B1,"//div[@class='orca-rating SwtJyda color-yellow tbody-6']/span"),3)

But tanaike's formula is very good.

answered Jan 3, 2023 at 9:27

TipVisor

1,10211 silver badges24 bronze badges

1 Comment

Lod Over a year ago

thanks for the INDEX example. I could retrieve all values instead of just the 1st one adapted like so:

=INDEX(REGEXREPLACE(IMPORTXML("https://www.forexfactory.com/calendar?month=jul.2023","//td[@class='calendar__cell calendar__impact impact calendar__impact calendar__impact--low']//span/@title"),"Impact Expected",""))

Collectives™ on Stack Overflow

Extract Substrings using importxml in google sheets

2 Answers 2

References:

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

References:

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related