Formula to search site and populate expiry dates

[ChrisFoster]

Hi all,

I have a list of ICO Registration numbers in column E on a worksheet.

Using Information Commissioner's Office - Register of data protection fee payers I can search for individual reference numbers and it provides information on that company such as address, contact details and the expiry date of the license/registration.
Using reference Z5347709 as an example, once the search button is pressed it takes me to URL Information Commissioner's Office - Register of data protection fee payers - Entry details - so basically

https://ico.org.uk/ESDWebPages/Entry/

and then the reference I am searching for.

What formula can I use to extract the date next to 'Registration expires:' on each reference number in column E rather than me having to manually search for each one?

Regards,

Chris

 

[Kerryx]

Hi Chris, did you try a search on this site i did something similar to this for someone earlier this month
Extracting Dates from a website -e.g. https://core.cro.ie/ (for each company in a list)

 

[Zaifee]

To extract the registration expiration date from the URL of the ICO website, you can use a combination of Excel's built-in functions, such as MID, FIND, and SUBSTITUTE. Here's an example formula you can use:

=IFERROR(MID(WEBSERVICE("https://ico.org.uk/ESDWebPages/Entry/" & E1),FIND("Registration expires:",WEBSERVICE("https://ico.org.uk/ESDWebPages/Entry/" & E1))+22,10),"")

This formula assumes that the ICO registration number you want to search for is in cell E1. You can adjust the cell reference as needed.

Here's a breakdown of the formula:

1. WEBSERVICE function: This retrieves the HTML content of the ICO website page for the specific registration number.
2. FIND function: This finds the position of the text "Registration expires:" in the HTML content.
3. MID function: This extracts the substring from the HTML content starting from the position of "Registration expires:" and captures the next 10 characters (assuming the date format is always consistent).
4. IFERROR function: This wraps the formula to handle cases where the registration expiration date is not found. It returns an empty string if there is an error.

Copy the formula to the adjacent cell of the ICO registration number, and it will extract the registration expiration date from the corresponding URL.

 

[ChrisFoster]

To extract the registration expiration date from the URL of the ICO website, you can use a combination of Excel's built-in functions, such as MID, FIND, and SUBSTITUTE. Here's an example formula you can use:

=IFERROR(MID(WEBSERVICE("https://ico.org.uk/ESDWebPages/Entry/" & E1),FIND("Registration expires:",WEBSERVICE("https://ico.org.uk/ESDWebPages/Entry/" & E1))+22,10),"")

This formula assumes that the ICO registration number you want to search for is in cell E1. You can adjust the cell reference as needed.

Here's a breakdown of the formula:

1. WEBSERVICE function: This retrieves the HTML content of the ICO website page for the specific registration number.
2. FIND function: This finds the position of the text "Registration expires:" in the HTML content.
3. MID function: This extracts the substring from the HTML content starting from the position of "Registration expires:" and captures the next 10 characters (assuming the date format is always consistent).
4. IFERROR function: This wraps the formula to handle cases where the registration expiration date is not found. It returns an empty string if there is an error.

Copy the formula to the adjacent cell of the ICO registration number, and it will extract the registration expiration date from the corresponding URL.

Thanks but that hasnt worked. The result I get is '/dt>'
Did it work fine for you?

 

[ChrisFoster]

Hi Chris, did you try a search on this site i did something similar to this for someone earlier this month
Extracting Dates from a website -e.g. https://core.cro.ie/ (for each company in a list)

Thanks, I'll have a look at this now.