English, Tutorials

Extracting Geographic Coordinates from Spreadsheet Cells [and Converting THEM]

Geographic coordinates are numbers used to locate elements, objects, places (etc) on the Earth’s surface. Our planet is vertical and horizontally divided in angles, which are called longitude and latitude, respectively.

Spreadsheets are a very good way to arrange a great range of information, including coordinates. But if they are used carelessly, it can bring headaches from other planets… Especially if you want to extract a piece of information from a cell, i.e. remove useless information.

Suppose you have a huge spreadsheet with many geographic coordinates (e.g. 28°40′39″S 49°22′11″W) and you need to separate the degrees, minutes and seconds from each other. This is necessary, if your GIS software only accepts decimal degrees. One way (the hardest way), is to edit each cell and copy and paste to another cell… boring and time consuming. Another way is to create a function (formula) that can do this for us!

Here, we will be using either Microsoft Excel or LibreOffice Calc (the formulas work for both). You might download a sample spreadsheet by clicking here (>> coordinates <<) to follow the steps.

Microsoft Excel IconLibreOffice Calc Icon

The functions we are going to use are described below:

  • FIND(a; b; c): It will return the position of the character/number you search for, where “a” is the character we look for; “b” is the cell which have our data; and “c” is the position it will start counting;
  • MID(a; b; c): It will extract a text/number from the middle of a cell, where “a” is the cell that contain our text; “b” is the start position; and “c” is the number of character we want to select;
  • IFERROR(a; b): Function to define what is going to happen if an error occurs, where “a” is what will happen if there is no error and “b” is what will return if we got an error;
  • RIGHT(a, b): Function to extract a text starting from the right side of the text, where “a” is the data containing cell and “b” is how many character we want to extract.
  • IF(a; b; c): It checks if a condition is true or false, where “a” is that condition; “b” is what happens when it is true; and “c” is what happens when it is false.

It is pretty simple to insert function in your spreadsheet. Just insert the equals sign (=) in the cell and type the function, followed by the parentheses and their arguments.

Now, let’s work on our spreadsheet. If you have downloaded our data, you will see that we have two columns (one with the latitudes and the other with the longitudes) in the exercise sheet. First, we will create some auxiliary columns; three columns with the following header: “degrees”; “minutes”; and “seconds”. Repeat the same step for the longitude data. After that, there will be six columns.

Next, two columns are added to tell in which hemisphere the point is located (see image below). This will help us to define later if the decimal degree is positive or negative.

Spreadsheet Example

After inserting the auxiliary columns, we will add our functions. In the degrees columns, the functions below will be inserted (again, one for the latitude and the other for the longitude):

=MID(A2;1;FIND("°";A2)-1)
=MID(B2;1;FIND("°";B2)-1)

This tells the MID function from where to extract the text (i.e. A2 and B2 cells) and where to start (i.e. position 1). The FIND function will return the position of the degree symbol in the cell, minus 1, because we don’t want to extract the degree symbol.

Let’s move to the next column, the minutes columns. We will still use the MID and FIND functions. However, this time we will use them to tell where is the degree symbol and where is the minute symbol.

=MID(A2;FIND("°";A2)+1;FIND("′";A2)-FIND("°";A2)-1)
=MID(B2;FIND("°";B2)+1;FIND("′";B2)-FIND("°";B2)-1)

The functions work the same way as before, but now, we indicate that the text extraction will begin after the degree symbol (thats explains why we added the number 1 to the position); and we will subtract the position of the degree symbol from the minute symbol, minus 1; this last operation (Degree symbol positioin minus Minute symbol position) will give us how many characters to extract, from the degree symbol to the minute symbol.

There is just one little change in the seconds columns (see below). We added the IFERROR function due to the fact that, sometimes, the seconds are omitted because they are equal to zero. And to prevent an error to occur, when there is no second symbols, our function will return zero.

=IFERROR(MID(A2;FIND("′";A2)+1;FIND("″";A2)-FIND("′";A2)-1);0)
=IFERROR(MID(B2;FIND("′";B2)+1;FIND("″";B2)-FIND("′";B2)-1);0)

So far, we have the degrees, minutes and seconds. Now we will extract the hemisphere. Since the hemisphere indication is in the last position, the RIGHT function will be used. Inside the two columns named N/S and E/W, you can paste the following functions:

=RIGHT(A2;1)
=RIGHT(B2;1)

Finally, we have separated all our data in different cells and we are ready to calculate the decimal degrees in another column. The following functions will be used:

=IF(J2="S";-1*(D2+(E2/60)+(F2/3600));D2+(E2/60)+(F2/3600))
=IF(K2="W";-1*(G2+(H2/60)+(I2/3600));G2+(H2/60)+(I2/3600))

As you can see, we used the IF function to express if we will multiply or not by minus 1 our decimal degree equation. This equation simply sums degrees with minutes divided by 60 and seconds divided by 3600.

This is how you convert geographic coordinates to decimal degrees coordinates using spreadsheets such as Microsoft Excel or LibreOffice Calc. Now you just need to drag the functions down from one row to apply them to the others.

If you have any question, feel free to comment. We will answer you as soon as possible.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s