Get Last ID from string using formula

[BroadbridgeDesign]

Hi all,

I need to extract an ID number from this piece of text

{"success":true,"data":[{"id":312677,"sitemap_id":22728,"status":"finished","test_run":0,"sitemap_name":"watch-scraper","time_created":1519922954,"jobs_scheduled":0,"jobs_executed":2,"jobs_failed":0,"jobs_empty":0,"stored_record_count":1,"request_interval":1000,"page_load_delay":2000,"driver":"fast","scheduled":0},{"id":312686,"sitemap_id":22728,"status":"finished","test_run":0,"sitemap_name":"watch-scraper","time_created":1519923749,"jobs_scheduled":0,"jobs_executed":2,"jobs_failed":0,"jobs_empty":0,"stored_record_count":1,"request_interval":1000,"page_load_delay":2000,"driver":"fast","scheduled":0}],"current_page":1,"last_page":1,"total":2,"per_page":100}

Basically, every day a new line gets added ({"id":312686,"sitemap_id":22728,"status":"finished","test_run":0,"sitemap_name":"watch-scraper","time_created":1519923749,"jobs_scheduled":0,"jobs_executed":2,"jobs_failed":0,"jobs_empty":0,"stored_record_count":1,"request_interval":1000,"page_load_delay":2000,"driver":"fast","scheduled":0}],"current_page":1,"last_page":1,"total":2,"per_page":100}}

I need an excel formula to extract the last ID each time (in this case, 312686). I have tried to trim either side of the last {"id":, but did not have any luck

Any help would be greatly appreciated.

 

[steve the fish]

Hi. Put your text in A1. Try this formula:

=0+LEFT(TRIM(RIGHT(SUBSTITUTE(A1,"""id"":",REPT(" ",LEN(A1))),LEN(A1))),6)

It assumes its always 6 numbers.