Brain Bender... Array formula not filling cells with FILTER function

2
votes

So I'm trying to extract specific data from line separated information.

City: Kitty
State: Bliss
Country: Narnia
Latitude: 89.1023
Longitude: -123.4567

I want to extract the actual Latitude coordinates on to 1 column and Long onto another. So I've built a function that Splits the data into separate cells, transposes it vertically, searches for "Latitude", tests for number, returns line with Latitude, takes # of characters from the left, then takes # characters from the mid after "Lat: " expression.

=mid(left(filter(TRANSPOSE(SPLIT(C2,CHAR(10))),isnumber(search("Longitude",TRANSPOSE(SPLIT(C2,CHAR(10)))))),20),12,11)

This formula works great. The issue is that this information coming in is inserted into the spreadsheet. And when values are inserted, they don't maintain the formulas in that cell. So any advice on how to keep the formula to filter out the Lat and Long #s that will work with inserted values? Apparently arrayformulas don't play well with filter functions...

Here https://docs.google.com/spreadsheets/d/1OZSDju3hRyGyRfFhHJT2PLQ3DBvcfOAT1ZvNxB-J0DQ/edit?usp=sharing is the link to the sheet...

2
arraysregexgoogle-sheetsgoogle-sheets-formulaarray-formulas

2 Answers

4
votes

use:

=ARRAYFORMULA(IFNA(REGEXEXTRACT(C2:C, "Latitude: (.*)\nLongitude: (.*)")))

enter image description here

2
votes

You can try the following formulas

For your Latitude values in cell F2 use

=ArrayFormula(IFERROR(REGEXREPLACE(REGEXEXTRACT(C2:C14,"(Latitude:.*)"),"Latitude:","")))

For your Longitude values in cell G2 use

=ArrayFormula(IFERROR(REGEXREPLACE(REGEXEXTRACT(C2:C14,"(Longitude:.*)"),"Longitude:","")))

enter image description here

Functions used: