Google Spreadsheet Extracting Text using ImportHtml

Question:

( by raymears )

I have no clue how to separate data pulled in from lists on a web page. Here is my spreadsheet:

My importhtml cell is E2, it references I2 for the URL.

All the data I am most interested in is in the 2nd cell of the returned list. (The one with Solo 5v5 at the top)

My goal would be to grab the second line as text for it's own cell [Silver III], and for the remaining four lines just grabbing the numbers. Telling Google Docs to ignore everything else seems tough, I don't even know where to start really.

Any insight would be a great help, googling query/select/where is pretty confusing since it never works with lists or cells with multiple lines, etc.

I don't even know if this is possible. If you want more information don't hesitate to contact me.

Thanks

Solution:

Have a look at the following screenshot:

I have the following formula in Cell B14:

=split(index(importhtml(I2, "list", 3),2);char(10))

I have the following formula in Cell B16:

=arrayformula(substitute(substitute(substitute(split(index(importhtml(I2, "list", 3),2);char(10));" League Points";"");" Wins";"");" Losses";""))

I have the following formula in Cell B18:

=query(arrayformula(substitute(substitute(substitute(split(index(importhtml(I2, "list", 3),2);char(10));" League Points";"");" Wins";"");" Losses";""));"select Col2,Col3,Col4,Col5,Col6")

The link in the Cell I2:
"http://www.lolking.net/summoner/na/20327085"

Here is the Webpage:

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive