Split a cell with coordinates into two columns with Latitude and Longitude only

Question

I am trying to split a cell with coordinates like 01.00000, 02.00000; 03.00000, 04.00000; 05.00000, 06.00000 into two columns LAT and LONG. In the first column, all Latitude In the second column, all Longitude Like

    |  Col A                                                     | Col B                      |    Col C                   |
    |------------------------------------------------------------|----------------------------|----------------------------|
    | 01.00000, 02.00000; 03.00000, 04.00000; 05.00000, 06.00000 | 01.00000,03.00000,05.00000 | 02.00000,04.00000,06.00000 |

I use the division formula if only one coordinates are written

=ArrayFormula(IF(ROW(B1:B)=1,"LNG LTD",IF(ISBLANK(A1:A),"",SPLIT(A1:A, ","))))

But I don’t know how to combine the rest now (I don’t really understand how it would be possible to combine everything together with CONCATENATE)

Is the first piece of raw data in A1 or A2? Do you need to do this for just one cell, or for every cell in Column A? — Erik Tyler
And how is the raw data getting into your sheet? Manually or automated? This can be done via formula; I just need to understand the layout and parameters fully. — Erik Tyler
Tai, are you interested in the formula solution for this issue? Or are you choosing to use the script version provided by NikkoJ? — Erik Tyler

Nikko J. Nikko J. · Accepted Answer · 2021-03-09T16:35:43

Try using custom function:

Code:

function myFunction(value) {
  var result = [];
  value.forEach(val => {
    var lat = [];
    var long = [];
    val.forEach(x => {
      var data = x.split(';');
      data.forEach(x => {
        var temp = x.split(",");
        lat.push(temp[0]);
        long.push(temp[1]);
      })
      result.push([lat.join(","), long.join(",")]);
    })
  })
  return result;
}

Output:

Reference:

Custom Functions in Google Sheets

Split a cell with coordinates into two columns with Latitude and Longitude only

1 Answers