Convert Latitude And Longitude To Decimal Degrees


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Convert Latitude And Longitude To Decimal Degrees  Excel

View Answers


Similar Excel Video Tutorials
Trig functions PI RADIANS DEGREES
 See how to convert Degrees to Radians with functions and formulas. See how to convert Radians to Degrees with functions and formulas.
Radians = D ...
Decimal Into Fraction
 See how to go from a decimal to a fraction
See how to go from a fraction to a decimal ...
Similar Topics
Hi All,
I'm currently working on a project with data that includes columns for both latitude and longitude which I will need to convert from its current form into decimal degrees. The problem is that the coordinates are in the format DDDMMSS followed by a single letter indicating the direction, which means I have to first extract proper degree minute second format before I can do anything. Further complicating the problem is that each of the entries does not necessarily have all of the components I just described (eg some of the latitudes only have degrees and minutes but not seconds) and also if a longitude is less than 100 degrees, the third digit is not used in the data. There are over 2000 records so doing the conversion by hand just won't work and I've only got a few days to present this part of my project anyway.
So, to sum up, I'm looking for a formula or series of formulas that will reliably convert something like 1083000W into 108.5000 when the last two zeros may or may not be there and the first digit may also be missing.
Thank you in advance,
Greg
I've just recieved a spreadsheet of x and y coordinated in easting and northing in cm. To be able to use this data I need to be able to convert this to Longitude and Latitude in degrees. I've looked at the excel functions but havn't been able to find anything that looks as if it will help is anyone able to come up with a macro solution?
Hi. I have a spreadsheet that calculates the distance between two points based on the latitude & longtitude of the points. There is a row that identifies what the shortest distance is between each set of coordinates. One set of coordinates also has an ID assigned to it. I would like to create a row to return the ID based on the shortest distance. I tried to create a vlookup but could not get a value to return.
Any advice on how this is possible?
Thanks!
Site ID
VG08483A
VG08010A
Latitude in Degrees
36.10836111
36.0957497
Longitude In Degrees
115.184527
115.18164
Latitude in Radians
0.630209789
0.62998968
Longitude In Radians
2.01034925
2.0102988
Nevada Towers
Distance to Data Point
Distance to Data Point
STIAID
Latitude
Degrees
Longitude
Degrees
Latitude
Radians
Longitude
Radians
2227067
36.10836111
115.1845278
0.630209789
2.010349257
0.000000
1.426155
Shortest Distance (km)
0.000000
0.000000
Shortest Distance (feet)
0
0
0
Cell with lowest value
$F$634
$G$412
STAAID w/lowest value
I am using Excel to calculate distances from latitude and longitude GPS positions. These positions have a format as follows:
S36 39.113 E174 25.945
These have been imported into a column, and using VBA I wish to separate
latitude and longitude into two other columns. In both cases the minutes
will eventually have to be express as decimals of the degrees. I don't
mind if numerous columns are used as stages to get the final answer
which is 36.234678 in one column and 174.15567 in the other.
This can be done one bearing at a time or in a loop.
Thanks Sandy
I'm creating a user form where the user can enter latitude and longitude in either Deg/Min/Sec format or Decimal Degrees.
To go from decimal degrees into Deg/Min/Sec I need to do the following
A=Decimal Degrees (Single)
X=Degrees (Integer)
Y=Minutes (Integer)
Z=Seconds (Single)
X = Integer(A) < I need to cast A as an integer or truncate it. Not sure how to do this in VBA.
Y = Integer[( A  X ) * 60] < Again, needs to be cast as an integer
Z = {[( A  X ) * 60]  Y} * 60
I didn't see a native truncate function in Excel and in the past I've always used Left() or Right()
How would y'all do this?
Not sure what to search for so apologies is this is answered somewhere else...please direct me to it if so.
I have a single column of data (2400 entries or so) that repeats:
City...
Zip Code...
Latitude...
Longitude...
City...
Zip Code...
Latitude...
Longitude...etc.
I need to separate it into 4 columns:
City Zip code Latitude Longitude
City Zip code Latitude Longitude
City Zip code Latitude Longitude
Ideas?
Need formulae to convert decimal degree (lattitude/longitude) into Degree,
Minutes, Seconds format.
I have the following:
Lattitude data of 0.1741463938 and to convert to decimal degrees, I muliply
by 180 which gives me decimal degrees of 31.3463508840. Now I need to
convert to a Degree, Minute, Seconds format (N34* 50' 56.04"). Note: the '*'
represents the 'degree' symbol  did not know how to display the symbol.
Need to also display whether 'N' (North) or 'S' (South) based on actual
coordinate.
For Longitude, I have data of 0.4758427618 *180 = 85.6516971240 (decimal
degrees) and need to also convert to Degree, Minute, Seconds format (W16* 40'
23.54"). Note: the '*' represents the 'degree' symbol  did not know how to
display the symbol. Need to also display whether 'W' (West) or 'E' (East)
based on actual coordinate.
The formulae needed will take the original lat/long data, multiplied by 180,
and then ???? applied to deliver the data in Degrees, Minutes, Seconds format.
Thank you very much for your assistance.
Tim
Help! Inexperienced with Excel.
Need Excel 2007 formula(s) to convert longitude/latitude coordinates such as "N483846.5" and "E0881914.4" into Degrees Minutes decimal format.
All longitudes are N, and all latitudes E. Long/lat have separate columns.
The N's and E's are followed by one space; I need those N's, E's, and spaces gone. All longitudes (all E) have a zero placemarker after E, which I assume needs to go, too.
Thank you!
I have created a form that converts latitude / longitude from Decimal Degrees to Degrees Minutes Seconds decimal seconds. I want my coworkers to be able to use the form, but I don't want them to see the excel window/worksheet when they open it up.... I thought I had received an email once that appeared to be an excel file but when you opened it, it was just a form. I have tried all I can think of to no avail. Any help would be greatly appreciated.
any one know where one can get a formula that will parse x,y coordinates from
degrees/minutes/seconds into decimal ?
Hi,
Dumb question. I pulled some data for latitude and longitude for some locations from mySQL database. I think they may be in some sort of ASCII or binary format Latitude (0.2123453) Longitude (0.3872453). Is there a formula to convert these to regular lat and long decimals in this format like 47.344 12.342??
Hi,
I have the longitude and latitude as below. I want to calculate the distance from A to B. With Excel, are there any formulars to do so? Please advice.
Longitude Latitude
A: 103.27686400 13.61228700
B: 102.56800000 13.68700000
Thanks you very much for your time.
sanlen
Is there any way to format a cell to show longitude or latitude?
I am working out the distances between various points based on longitude and latitude. I have entered the coordinates as follows;
lat1 46:34:21
lon1 00:56:13
lat2 48:34:21
lon2 00:56:13
but when I try to edit the coordinates the formula bar shows them as follows;
lat1 01/01/1900 22:34
lon1 00:56:13
lat2 02/01/1900 00:34
lon2 00:56:13
Does anyone know why the latitude coordinates are displayed like this but the longitude stays as entered?
Thanks
Is there a function or list of code in VBA that will convert a Military Grid Reference System (MGRS) grid to Longitude/Latitude? What is the best way to do this conversion?
I'm trying to speed up some work on an excel sheet. The sheet converts degrees latitude and longitude to Northing and Easting coordinates, but right now we have to type them in directly.
For example, the degrees would be 81.0056 and I want to reference just the .0056 from the cell that contains the whole 81.0056 degrees. Is there any way to just reference character place three through seven of that cell? All I'll need it to do is copy it over, the rest of the program will convert it for me.
Thanks
I recieved a spreadsheet that has about 200 longitude and lattitude positions in degrees minutes seconds. I need to convert them into decimal degrees. Anyone know of a formula that will do a batch :o conversion and not requre me to enter each long/lat by hand? Any input would greatly be appreciated!! Thanks.
Dear all,
I have around 30 files, and each of these contain millions of rows (I'm using Excel 2007), and three columns. Column one = longitude, column two = latitude, column three = value.
I have a reference file of the same format which has all the 'master' longitude and latitude values. The other files have less rows, since they do not contain all the longitude and latitude values that are present in the reference file. What I want to be able to do is for the files with less rows, compare each row to the reference file, and if a latitude and longitude coordinate for a particular row is not present, then insert this in place, and give the value (column 3) as zero. Where present, values in columns 1 and 2 should be in sequence (i.e. follow the same pattern as in the reference file), so where the sequence is 'broken', a latitude and longitude can be assumed missing, and a new row inserted with the 'missing' longitudes and latitudes, with zero in column 3.
I hope I've made myself clear enough  if not, please do not hesitate to get in touch with me.
Many thanks,
Steve
i have a set of latitude and longitude data. we will just talk about the latitude data. it is in a onecellwide column in the following style:
40:11:00
38:03:06
etc
there are hundreds of rows of this.
thats
degrees:minutes:seconds
i need to convert this to decimal degrees
that should not be hard, all i have to do is use the "text to columns" utility and break that single column into 3 columns then i will have the following:
column a column b column c
40 11 00
then i make a column d such that column d =(column a)+([column b]/60)+([column c]/3600) to get
column d
40.183333
for example.
However, and this is my problem, when i try to use the "text to columns" utility, Excel converts my values into dates, for example 40:11:00 becomes
column a column b column c
1/1/1900 4 11 12:00 PM
I understand what it is doing, it is seeing my originl data and assuming that i want it converted into date values.
when i try to reformat the cells prior to splitting it into 3 columns, (say reformat to "general" ) it just converts my number into a julian date (the above example becomes 1.6...)
i cant seem to figure out hoiw to tell excell to just take the numbers as i have entered them and STOP TURNING THEM INTO DATES.
Any suggestions?
please email me at amking@ucdavis.edu
thanks
Aaron King
Hi...
I regularly import commadelimited data files (that include a header "row") that are comma delimited and contain latitude and longitude values, along with many other fields. It would be prefererable to double click on the .csv file to open in in Excel, rather than going thru the import process where you define each field type. I've never fully understood how Excel guesses at the proper formatting for those fields when opening the csv's with the double click method, but it generally does a good job.
In its native state in the .csv file, a typical latitude value might be 46:27 (fortysix degrees north, 27 minutes, and no seconds). This gets imported into Excel as a date/time value. The underlying serial value yields date/time 1/1/1900 10:27:00 PM, but it displays "correctly" as 46:27:00. Not perfect, but this works for me.
Occassionally, tho, I run into a problem with longitude coordinate values. A native longitude value might be"116:48" (one hundred sixteen degrees west, 48 minutes, and no seconds). Upon import, this gets changed to =48:116 and displays the #VALUE! error message. I'm not sure why it gets interpreted as a formula, nor why the minutes and degrees get reversed. I think the error message is understandable, since Excel does not like negative time values. And, sometimes, those longitudes that try to import as time values result in a circular reference error. The funny thing is, many of the longitudes values do import correctly. For example, native 155:29:00 imports as 155:29:00 (but isnumber returns false, so I guess it's a text value).
Any ideas as to what is happening here and how I can prevent it (short of manually importing these files each time)?
Thanks!
SDL
i have a column of info in degrees/min/sec
example: 254802N 0503311E
How or can vert this column to decimal degrees?
Hello All,
I have been working on spreadsheets and have come across a roadblock. Let me propose my problem.
I am working with a spreadsheet that contains the coordinates of 240 places, in longitude and latitude (in decimal degrees). We will call this target sheet.
I have another spreadsheet that contains the coordinates of 900 places, in longitude and latitude (in decimal degrees). We will call this reference sheet.
Goal 1  Calculate the great circle distance between a set of coordinates on the target sheet with the coordinates on the reference sheet. I am using the spherical law of cosines to do this, and i am success when calculating one set of coordinates (target sheet) agianst a set of coordinates (reference sheet)
spherical law of cosines
d = acos(sin(lat1).sin(lat2)+cos(lat1).cos(lat2).cos(long2−long1)).R
excel formula =acos(sin(lat1*pi()/180)*sin(lat2*pi()/180)+cos(lat1*pi()/180)*cos(lat2*pi/180)*cos(lon2*pi/180lon1*pi()/180)*6378.135
However, my ultimate goal is to:
1. Calculate the distance of a set of ONE coordinates from the target sheet agianst the ENTIRE set of coordinates of the reference sheet and returning the value having a distance of less than 50 kilometers, cannot equal zero and is the MINIMUM value of the array. When i try to create a formula that covers all these requirments i get errors and the syntax for such a large formula hurts my brain!
It is quite hard to put it into words but hopefully i've got my point across. Help would be extremely appreciated.
geo
I have three columns in Excel and each column has fifty rows. The first column is about longitude and the second column is also latitude, and the third one is amount of rain for each station. I want to select the amount of rain by specific longitude and latitude. For example I want to choose the amount of rain for stations which the latitude is between 3.20032 and 4.30618; the longitude is between 101.59653 and 102.70441. I have to use the macro language in Excel for copy the three rows include longitude, latitude that what I need and amount of rain in some stations to other column. I have to write 5 formula, because I need a 5 area by specific ID stations
Could you please help me to doing it?
I highly appreciate your kind cooperation
Jamil
I have 2000 building addresses throughout Toronto. I've created geographic areas and need to figure out how many buildings fit into each geographic area... I'm not exactly sure the best way to do this...
One way is to figure out the equation for finding the distance between two points, given the latitude and longitude of both locations. For example: what
is the formula one would use to find the distance between Building 1 (43.1556978 latitude, 79.2123856 longitude) and Building 2(43.6639513 latitude, 79.3836226 longitude.)?
But i'm looking for a faster way to sort the buildings, but area...
Any help/suggestions would be greatly appreciated!
Thanks,
I have three columns in Excel and each column has fifty rows. The first column is about longitude and the second column is also latitude, and the third one is amount of rain for each station. I want to select the amount of rain by specific longitude and latitude. For example I want to choose the amount of rain for stations which the latitude is between 3.2 and 4.3; the longitude is between 101.5 and 102.7. I have to use the macro language in Excel for copy the three rows include longitude, latitude that what I need and amount of rain in some stations to other column. I have to write 5 formula, because I need a 5 area by specific ID stations.
Thanks to anyone and everyone that helps.
Jamil
Hey all, I'm kind new to excel's vb, but I tried to work this out before I posted. Anyways, I'm trying to build a macro that will do two things;
1) I have Latitude and Longitude data in a workbook. One Longitude is in column B and Latitude is in Column C. This data is collected about every second so there can be up to 500 pairs of latitude and longitude I want to match up and save in an individual file.
2) Save the workbook based on the data that is placed in a column, ie Cells A1 to A500. So that I would have 500 individual files with the names from column A. Oh, and I'd save the file as a .txt (no problem there)
So when you look at the big picture I want to save 500 .txt files with names found in column A and have those files contain a single Latitude and longitude value found in columns B and C.
Can anyone help? Thanks