Search code examples
google-sheetsdatasetgoogle-docs

Is there a way to delete everything except the company names in this dataset on Sheets or Docs or somewhere else?


I have a dataset from a view-source of a map from a website. All the locations on this map are stored in one giant long text string that looks as follows (with no deviation from what I can see). This is just a single long line of text and there are no spaces or columns between each company name.

var lomdvm = [{"lat":36.7871842711066,"lng":-76.0567609375612,"weight":null,"iconUrl":"colour004.png","isUserMapPin":false,"isNewIcon":false,"title":"17TH STREET SURF SHOP","desc":"","label":"","index":0,"searchText":"17th street surf shop "},{"lat":36.8280972500721,"lng":-76.0650097012816,"weight":null,"iconUrl":"colour004.png","isUserMapPin":false,"isNewIcon":false,"title":"17TH STREET SURF SHOP","desc":"","label":"","index":1,"searchText":"17th street surf shop "},{"lat":39.0726029961708,"lng":-76.5304050205883,"weight":null,"iconUrl":"colour004.png","isUserMapPin":false,"isNewIcon":false,"title":"2 MORROW'S DAWN","desc":"","label":"","index":2,"searchText":"2 morrow's dawn "},{"lat":36.0418239869372,"lng":-115.223589015763,"weight":null,"iconUrl":"colour004.png","isUserMapPin":false,"isNewIcon":false,"title":"24 Outdoor LLC","desc":"","label":"","index":3,"searchText":"24 outdoor llc "},{"lat":25.9272451870142,"lng":-80.1560559818092,"weight":null,"iconUrl":"colour004.png","isUserMapPin":false,"isNewIcon":false,"title":"247 TEE SHIRTS","desc":"","label":"","index":4,"searchText":"247 tee shirts "},{"lat":28.1118815062855,"lng":-82.4294251104769,"weight":null,"iconUrl":"colour004.png","isUserMapPin":false,"isNewIcon":false,"title":"2502 PRISCILLA CT","desc":"","label":"","index":5,"searchText":"2502 priscilla ct "},{"lat":30.2775719902839,"lng":-85.9968439890057,"weight":null,"iconUrl":"colour004.png","isUserMapPin":false,

Is there a way to extact just the company names from this list? The company name always follows "title" and then another set of quotes for the company name if it helps.

I feel like there must be someway to do this in google docs or sheets but I'm coming up blank. Any help would be appreciated.

Thank you for taking the time to read.

Tried experimenting with some regexreplaces in Google Sheets but no luck.


Solution

  • You may try:

    =split(regexreplace(A1,"(^|"").*?($|title"":"")","|"),"|")
    

    enter image description here