Extracting multiple sections of text from google sheets cell

Question

I'm trying to grab a subset of covid data from a cell in google sheets that contains a lot of superfluous data.

Specifically, the LDH vaccine information tab here has a tab containing "Vaccination by Gender by Parish" which has two things repeated 64 times: A line containing information on total series initiated and completed per parish, and a chart showing the percentage breakdowns by gender for that parish.

I'm importing this tab by copy/pasting into a google form (splitting it in 2 halfway through to get around the character limit). It returns a massive cell (or, two cells rather) containing 32(x2) instances of both the lines containing parish vaccine data and several lines containing the chart data.

Is there a way I'm not seeing to create an array formula that will isolate the two segments? I don't need the gender breakdown information, just the top-line numbers per parish. If I can get the top-line numbers into a separate cell together, I can use a split function on them, but can't separate everything correctly using the split function from a single cell.

This is the shortest of three lists/charts that display the information as a raw string (on the front-facing side, which I need to use because other people will be inputting this data into the sheet).

Edit: Here's a viewable version of the sheet.

To get to the correct tab, click "Vaccination Information" at the top of the ArcGIS dashboard. Then, click the left arrow at the bottom of the dashboard until it displays "Vaccination by Gender by Parish."

If I understand correctly you are now using copy-paste to import data. Do you want the data to be loaded into the sheet automatically using import functions such as IMPORTHML ()? or a script? If so, it will not be easy to do, because the data is displayed on the page using a javascript. You can see for yourself if you disable javascript on the source page. — Boris Baublys
– Boris Baublys, Commented Jul 25, 2021 at 17:32
Yeah, I've tried scraping the data but have come to the conclusion that it's too difficult to manage, especially on a project that other people are contributing to when I'm not able to. It's a simple copy/paste job into a google form that imports into the sheet. My goal is to separate the data once it's in the google sheet without creating a stupid amount of useless rows. — nunciative
– nunciative, Commented Jul 25, 2021 at 19:05
Can you break down your problem to a minimal reproducible example? — Aerials
– Aerials, Commented Jul 27, 2021 at 8:20

Boris Baublys · Accepted Answer · 2021-07-25 20:04:36Z

1

Since it is not possible to use automatic import using formulas and you have to use copy-paste, you can get the result you want faster this way.

Copy from the site and paste the data on the sheet.
Filter and remove unnecessary rows.
Split the text into columns using the "-" separator.
Search and replace, remove text and spaces from the data columns.

answered Jul 25, 2021 at 20:04

Boris Baublys

1,22514 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Extracting multiple sections of text from google sheets cell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related