Draft, 12 April 2016
This page should be in a useful state, but still needs work before it's finished.
You’ve got a CSV file containing geolocated place data that you want to import into mapping program, but the coordinates are combined in a single column. How can you separate the latitude and longitude into separate columns?
Here’s a CSV file plotting locations of bus stops in Canberra. If you open it up you’ll see that the
stop_lat field actually contains both the latitude and longitude. Let’s split them into separate columns.
stop_latcolumn header (Column E in this example) and click on the down arrow. Choose ‘Insert 1 right’ to add a new empty column.
stop_latfield. In this example it would be cell F2.
=SPLIT(E2, ", "). Hit enter. Magic! The contents of E2 have been split between F2 and G2.
SPLITfunction takes two parameters: the thing you want to split into parts, and a delimiter (a combination of characters) that tell the function where the split should take place. In this example, we’re splitting the text in E2 using “, “ (comma and space) as the delimiter.
All that’s left is to remove the brackets from our new columns. First we have to replace the formulas in our new columns with their calculated values.
(in the ‘Find’ box and click ‘Replace all’.