Hello, I have some data in rows but they are not organized in the proper columns.
I'd like to search the row for keywords, parse and extract the information in the cell to build a new table.
Here's an example of the raw data:
<tbody>
[TD="class: xl64"]
<tbody>
[TD="class: xl64"]
<tbody>
[TD="class: xl65"]
<tbody>
[TD="class: xl67"][TABLE="class: grid, width: 500"]
<tbody>[TR]
</tbody>
[/TD]
[TD="class: xl67, width: 193"][/TD]
[TD="class: xl66"]I would like a the data extracted under the headings "Desert", "Price" and Points. For example under the heading Desert, I would like the formula to search the first row of the raw data and return "Cake" in A2," $2.50" in B2 and "200" in C2. The same for the other rows in the data.[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
</tbody>
[/TD]
[TD="class: xl66, width: 394"][/TD]
[TD="class: xl66, width: 394"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][TABLE="class: grid, width: 500"]
<tbody>[TR]
</tbody>
[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]Thank you.[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"][/TD]
</tbody>[/TD]
[TD="class: xl64, width: 193"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="class: xl64, width: 193"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]
I'd like to search the row for keywords, parse and extract the information in the cell to build a new table.
Here's an example of the raw data:
<tbody>
[TD="class: xl64"]
A | B | C | |
1 | Desert | Price | Points |
2 | Cake | $2.50 | 200 |
3 | Ice cream | $1.50 | 100 |
<tbody>
[TD="class: xl64"]
<tbody>
[TD="class: xl65"]
A | B | C | |
1 | $2.50 | desert: Cake | points:200 |
2 | points: 100 | $1.50 | desert: Ice cream |
<tbody>
[TD="class: xl67"][TABLE="class: grid, width: 500"]
<tbody>[TR]
</tbody>
[TD="class: xl67, width: 193"][/TD]
[TD="class: xl66"]I would like a the data extracted under the headings "Desert", "Price" and Points. For example under the heading Desert, I would like the formula to search the first row of the raw data and return "Cake" in A2," $2.50" in B2 and "200" in C2. The same for the other rows in the data.[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
</tbody>
[TD="class: xl66, width: 394"][/TD]
[TD="class: xl66, width: 394"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][TABLE="class: grid, width: 500"]
<tbody>[TR]
</tbody>
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]Thank you.[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"][/TD]
</tbody>
[TD="class: xl64, width: 193"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="class: xl64, width: 193"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]