Replacing S and W with minuses in coordinates

If your data is geocoded using latitude and longitude, it may be presented using the letters N, S, E and W along with the numbers.

We at Mapsdata use the – in our opinion better – standard of adding a minus to coordinates of points that are South of the equator or West of Greenwich.

As a result, we don’t write: 51.5607°N, 0.1557°E, but simply: 51.5607, 0.1557.
And we don’t write: 12.0608°S, 77.0361°W, but simply: -12.0608, -77.0361.

If the coordinates in your data use N, S, E and W instead of positive and negative figures, here is how to fix it:

The example below is a spreadsheet listing the districts (known as boroughs) of London, their administrative headquarters and their coordinates, which we created based on data from Wikipedia.

For this to be useable with Mapsdata, we needed to replace values that are west of Greenwich, say 0.1517°W, with coordinates using minuses, like -0.1517. We also need to remove the Ns, Es and Ws. As these points are in London, there are no values South of the equator, but the logic and the process would be exactly the same.

One way to do this is to separate each column into two (use the “Text To Columns” tool as explained here, with “ ° “ as the separator) to get a table like this:

Then use the IF formula in Excel — it also exists in Apple’s Numbers and Open Office’s Calc.

The idea is to ask your spreadsheet: If there is a W in F2 (and F3, etc.) then put a minus before the longitude in E2 (and E3, etc.), and leave it unchanged if not.

In our example, we’ll type =IF(F2=”W”,-E2,E2). Then fill down:

If your coordinates also include southern latitudes, repeat the process with the latitude column.

Here, however, all that is left to do is to remove the data you don’t need. Before doing this though, make sure the new longitudes you have just created are values, and not formulas referencing the old longitudes. Otherwise, by removing the “old” data you would lose the “new”.

One way to make sure they are values is to copy them and then paste them back as values. (Use “Paste special” in the “Edit” menu in Excel to do this). Then make sure the columns have the correct headings. And your data should be ready to use, like this:

Next and last step, go to Mapsdata, upload your file and create your own data visualization.

Try now

If you’re still having problems creating your data visualization, please check our How to Use Mapsdata page.

If you can’t find the solution to your problem, do send us a line on, we’re always happy to help.