Google Sheets trying to extract certain information

accracer

New Member
Joined
Apr 17, 2024
Messages
1
Platform
  1. MacOS
Hi,

After some advice on what formula I would need to extract a certain bit information from a JSON file.

In the JSON file, what I need to do is search for a certain ID that has multiple entries, and then extract a portion of that data.

JSON information below

"carId": 2017,
"driverIndex": 0,
"laptime": 246470,

"carId": 2015,
"driverIndex": 0,
"laptime": 253627,

"carId": 2006,
"driverIndex": 0,
"laptime": 251875,

This is a portion of the data, and this multiplies for x amount of times. i.e that top 3 lines of data can multiple for 10 lines, where as the 2nd and 3rd may only repeat for 4 times. What I need to do is be able to type in the CARID number (top line of data) and then the formula will scan the full document or text and then I need it to extract the only 1 line of data which is LAPTIME.
Example, if I enter "carId": 2017 in to a search box, the formula runs and then it finds and pastes just the information need which is "laptime": 246470, but I only need the numeric values.

so I should look like this on the google sheet document
Search - "carId': 2017
Laptime - 246344
247855
239985

Is this even possible? and is it multiple formulas?

If someone could point me in the right direction of which formula to use or knows of guide that explains me needs, tat would be great
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Forum statistics

Threads
1,224,813
Messages
6,181,107
Members
453,021
Latest member
Justyna P

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