Finding nth occurrence and displaying information within.

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
801
Office Version
  1. 365
Platform
  1. Windows
Hello all,

I have a block of HTML that I need to table'ise in Excel.

The format of this data is unfortunately a gigantic block of HTML code with the relevant fields scattered within. It looks like this.

[TABLE="width: 326"]
<tbody>[TR]
[TD]
Mrs F Ake
12 Madeup Villas
Phoneyton
City of Not
FF4 K3Y
01234 567891

<tbody>
</tbody>
Mrs Ake would like the following rooms:

  • 1 Twin
Additional Notes: ONLY WANTS THIS HOLIDAY AND A TWIN ROOM.
For tour: 123456 -
Staying at: Selected Hotel, Yorkshire
on Saturday 19 March 2016 for 2 nights
Picking up from City of Not

<tbody>
</tbody>
[/TD]
[/TR]
</tbody>[/TABLE]

You may have to quote my post to get the HTML tags as this forum auto-displays them.


What I would like to do is separate all the fields into columns which have the following headers:

Title
Initial
Surname
Addr1
Addr2
Addr3
Addr4 (If there is a result)
Postcode
Phone
Room Req
Tour
Pickup


Thus, the following data would be as follows:

Title - Mrs
Initial - F
Surname - Ake
Addr1 - 12 Madeup Villas
Addr2 - Phoneyton
Addr3 - City of Not
Addr4 -
Postcode - FF4 K3Y
Phone - 01234567891
Room Req - Twin
Tour -123456
Pickup - City of Not


So how to do this.. I think if I can search for n instances of {td}%{/} (where { is < and % is wildcard) then I could potentially identify each field, then assigning each field to each column. So in the above example, the first instance of {td}%{/td} is "Mrs F Ake" then I could split each string out via the spaces into the relevant columns.


What do you think, is this possible?
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top