HTML list <ul> <li> to excel

[klig]

Hi

Got this list of expenses from Deliveroo website that I want to convert to a table on excel.

List is much longer but this is a sample of it:

<ul class="OrderList-6aecefdea7ffd811"><li class="OrderList-07f39044da27aa00"><a href="/orders/514304033" class="OrderList-7c201d26dad8601e"><div class="OrderSummary-cc1c40892bf3851f"><span class="OrderSummary-99f71e1ecb590f4d"><div class="ccl-45bd106b75353ec9"><div class="ccl-d5fa9efd412993ea"></div></div></span><div class="OrderSummary-076403350739e281"><p class="ccl-19882374e640f487 ccl-1daa0367dee37c3b ccl-a5fb02a1085896d3 ccl-9d0a5327c911d0f3 ccl-6b45f37d4cb665b6">Keu Banh Mi Vietnamese</p><p class="ccl-19882374e640f487 ccl-417df52a76832172 ccl-dfaaa1af6c70149c ccl-9d0a5327c911d0f3 ccl-6b45f37d4cb665b6">Delivered</p><p class="ccl-19882374e640f487 ccl-417df52a76832172 ccl-dfaaa1af6c70149c ccl-9d0a5327c911d0f3 ccl-6b45f37d4cb665b6">£ 29.79 • 26 January 2021</p></div></div></a><a href="/orders/514304033" class="OrderList-8b4c2aafa3ca4d96"><span class="ccl-462400680c526774 ccl-abe5c41af1b9498e"><svg height="24" width="24" viewBox="0 0 24 24" class="ccl-0f24ac4b87ce1f67 ccl-abe5c41af1b9498e ccl-c738ab1fde928049"><path d="M7.11621 19.1161L8.88398 20.8839L17.7679 12L8.88398 3.11612L7.11621 4.88389L14.2323 12L7.11621 19.1161Z"></path></svg></span></a><div class="OrderList-83f093c21847992c"><div class="OrderList-9c5e5f5a680b6357"><span class="ccl-67e0c7f3fe50cf69 ccl-a97a150ddadaa172"><button type="button" class="ccl-d0484b0360a2b432 ccl-5f83ec16556720ae ccl-ed9aadeaa18a9f19 ccl-a97a150ddadaa172" tabindex="0"><span class="ccl-cce251427bbe4ec4">Order help</span></button></span></div></div></li><li class="OrderList-07f39044da27aa00"><a href="/orders/513298709" class="OrderList-7c201d26dad8601e"><div class="OrderSummary-cc1c40892bf3851f"><span class="OrderSummary-99f71e1ecb590f4d"><div class="ccl-45bd106b75353ec9"><div class="ccl-d5fa9efd412993ea"></div></div></span><div class="OrderSummary-076403350739e281"><p class="ccl-19882374e640f487 ccl-1daa0367dee37c3b ccl-a5fb02a1085896d3 ccl-9d0a5327c911d0f3 ccl-6b45f37d4cb665b6">KFC</p><p class="ccl-19882374e640f487 ccl-417df52a76832172 ccl-dfaaa1af6c70149c ccl-9d0a5327c911d0f3 ccl-6b45f37d4cb665b6">Delivered</p><p class="ccl-19882374e640f487 ccl-417df52a76832172 ccl-dfaaa1af6c70149c ccl-9d0a5327c911d0f3 ccl-6b45f37d4cb665b6">£ 22.26 • 24 January 2021</p></div></div></a>

Can you please help?

Thanks

 

[Trevor G]

Hi *Klig welcome to the board.

If you put your list into Notepad or something like it and save the file with an HTML extension it would create a webpage which you could then copy and paste the data into Excel but the example you provided splits it in to a lot of rows.

Book1
A
1
Keu Banh Mi Vietnamese
2
3
Delivered
4
5
£ 29.79 • 26 January 2021
6
7
Order help
8
9
KFC
10
11
Delivered
12
13
£ 22.26 • 24 January 2021
14
Sheet1

 

[klig]

Hi and thanks for the reply.

Yes that's the problem, I need date and amount in separate columns. Do you know how to do this?

 

[Trevor G]

If you use find and replace in Notepad you can get it to change the circle to a div method. Perhaps looking at W3School website might help its a website for builidng HTML.

Links.xlsm
E
1
Keu Banh Mi Vietnamese
2
3
Delivered
4
5
£29.79
6
7
26-Jan-21
8
9
Order help
10
11
KFC
12
13
Delivered
14
15
£22.26
16
17
24-Jan-21
Sheet1

HTML Tutorial (w3schools.com)

 

[Norie]

How about using Power Query?

This worked for the data you posted.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Column1", Splitter.SplitTextByDelimiter("<li", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type1",1),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Top Rows", "Column1", Splitter.SplitTextByDelimiter(">", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11", "Column1.12", "Column1.13", "Column1.14", "Column1.15", "Column1.16", "Column1.17", "Column1.18", "Column1.19", "Column1.20", "Column1.21", "Column1.22", "Column1.23", "Column1.24", "Column1.25", "Column1.26", "Column1.27", "Column1.28", "Column1.29", "Column1.30", "Column1.31", "Column1.32", "Column1.33", "Column1.34", "Column1.35", "Column1.36", "Column1.37", "Column1.38", "Column1.39"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11", "Column1.13", "Column1.15"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Removed Columns",{"Column1.16", "Column1.14", "Column1.12"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Other Columns","</p","",Replacer.ReplaceText,{"Column1.16", "Column1.14", "Column1.12"}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Replaced Value", "Column1.16", Splitter.SplitTextByDelimiter("•", QuoteStyle.Csv), {"Column1.16.1", "Column1.16.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Column1.16.1", Currency.Type}, {"Column1.16.2", type date}})
in
    #"Changed Type2"
'

 

[klig]

Can you please explain how to use power query or point to the right instructions? Gave google a shot but didn't find anything clear. Some say it's an add on, others that it's built in. I use Excel for mac v16.45. Thanks

 

[Norie]

I think Power Query is available in Excel for Mac but not sure for which versions.

If it's available you should find it under Data>Get & Transform Data.

If you do find it select the From Table/Range option.

When Power Query opens goto View>Advanced Editor and replace what's there with the code I posted.