split GPS coordinates into 2 columns

[Sallieu Dumbuya]

Hi All,
Nice and thanks for all your support. I have a column for GPS coordinates, which i want to split into two columns - Longitude and Latitude. Can anyone help me with step-by-step procedures on how to do it?
appreciate any early response.
thanks once more

[Simon Berry]

Hi

In the past I have done this in three steps within Excel. There are probably more elegant ways!

Column A	Column B	Column C	Column D
-13.53606109115209 32.57124129685843 975.3160268517747 4.0	-13.53606109115209	-13.53606109115209 32.57124129685843	32.57124129685843

Column A is the data from CommCare
Column B is this formula: =LEFT(A1,FIND(" ",A1))
Column C is this formula: =LEFT(A1,FIND(" ",A1,25))
Column D is the formula: =RIGHT(C1,LEN(C1)-FIND(" ",C1))

Column B = Latitude
Column D = Longitude

Once you're happy the formulas are working correctly you can Copy Columns B to D and do a 'Paste Special... > Values' onto themselves to get rid of the formulas.

Best of luck.

Simon

[Jos van der Ent]

Hi Sallieu,

The method stated by Simon works, but if you are using Excel you could also use the 'text-to-columns' function which can be found under the data tab.

First you have to add the columns on the right of the said column.

Than select the whole row and go the text-to-column option

Choose delimited

make sure to choose 'space' as delimiter and finish up the dial screen.

Regards Jos

[Simon Berry]

Thanks Jos!

One learns something every day!

Simon