How to convert list of HTML table tags into Google sheet Table using formula?-CodePudding

In one cell, I run have =IMPORTDATA("https://www.mcb.mu/en/handler/GetIndicativeForex")

This results in a list of HTML table codes below.

<table >
<tr>
<th><span >Currency</span></th>
<th >Buy</th>
<th >Sell</th>
</tr>
<tr>
<td>AUD</td>
<td >31.32</td>
<td >32.57</td>
</tr>
<tr>
<td>EUR</td>
<td >45.75</td>
<td >47.12</td>
</tr>
<tr>
<td>GBP</td>
<td >54.48</td>
<td >56.11</td>
</tr>
<tr>
<td>JPY</td>
<td >33.39</td>
<td >34.73</td>
</tr>
<tr>
<td>USD</td>
<td >44.90</td>
<td >45.80</td>
</tr>
<tr>
<td>ZAR</td>
<td >2.68</td>
<td >2.85</td>
</tr>
</table>

How to convert this into a clean table like the image below using only formula?

I did it with the App script before but I wanted to stop using script, just excel formula.

CodePudding user response：

just a suggestion, is it possible to create and save it as an HTML file, upload it to a remote webserver and reference it via its URL using the IMPORTHTML formula =IMPORTHTML("yourdomain.com/tablename.html", "table",0)

CodePudding user response：

You can just use