If you do much geographic work, you may wonder if you can use Excel to display longitude and latitude in a cell in terms of degrees, minutes, and seconds. There are three ways that a solution can be approached.
First, if you just want to affect the display, you can follow these steps:
Figure 1. The Number tab of the Format Cells dialog box.
Now, if you type a number such as 1234543 into the cell, it is displayed as 123 degrees, 45 minutes, and 43 seconds.
Sometimes, however, you may want to take a decimal value that represents latitude and longitude and display it in degrees, minutes, and seconds. For instance, you may want 122.44 (which is a decimal representation of degrees) to be displayed as 122 degrees, 26 minutes, and 24 seconds. This cannot be accomplished with formatting the cell in which the number is contained. Instead, you must use a formula to achieve the proper display. For instance, if 122.44 is in cell A7, then you can put the following in cell B7:
=TEXT(TRUNC(A7), "0" & CHAR(176) & " ") & TEXT(INT((ABS(A7) - INT(ABS(A7)))*60), "0' ") & TEXT(((((ABS(A7)-INT(ABS(A7)))*60) - INT((ABS(A7) - INT(ABS(A7)))*60))*60), " 0''")
This is a long formula, but it provides the desired formatting of the latitude or longitude value. The result is text, and cannot be used in any calculations. If you want to use a display instead, you can simply divide the decimal value of the latitude or longitude by 24, which converts it into the same value ranges used by Excel to represent times. Then you can format the display of the formula as follows:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9457) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Displaying Latitude and Longitude.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Want to figure out if the text in a cell is bold? The best approach is to use your own user-defined function, as ...
Discover MoreWhen your macro is processing information in a worksheet, do you need to periodically make the contents of a cell bold? ...
Discover MoreIf you need to determine the font applied to a particular cell, you’ll need to use a macro. This tip presents several ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2019-10-13 20:53:31
Justin Guy
FYI, instead of CTRL+SHIFT+F, you could use CTRL+1 to open the same dialog, and saving yourself one whole keypress in the process!
2019-03-11 16:44:16
Ron
The most straightforward way to display latitude and longitude is to simply use the decimal format. For instance, it makes it easier to calculate distance between two points in Excel. It is also more intuitive and that's what many navigation systems (e.g., Google Maps) have evolved to. However, in Excel, it is challenging to convert the traditional nautical format to the more universal decimal format.
2016-09-14 07:51:05
MIchael Armstrong
If I enter the data as
35:51:21.6
Excel thinks it's a time, and displays it as
51:21.6
(where the 35 went is left as an exercise for the student)
But then if format the cell using the last formula in the tip i.e.:
[h]° mm' ss.0''
it displays as
35° 51' 25.6''
2016-09-13 17:44:47
Ritienne Gauci
Hello
I used your example to type in co-ordinates.
The only problem is that my degree co-ordinates are in the tens
35 degrees, 51 minutes and 25.6 seconds
It is typing it
355° 12' 56.0''
Can I ask you to help me to find the right code? I tried typing 0 before 25 but no go.
RG
2015-07-03 10:33:12
Hi Allen
I inherited a excel file that uses lat/long positioning and now want to change a few things. When setting up the series the lat and long is easy (x and y axis), but to display the name of each lat and long at the specified position, I cannot figure that one out. Do you perhaps have advise. Much appreciated your trouble.
Gallie
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2021 Sharon Parq Associates, Inc.
Comments