Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:


Advertisements


Free Excel Forum

Convert Latitude And Longitude To Decimal Degrees

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Convert Numeric Dollar Values into Text in Excel - UDF
- Convert a numeric dollar amount into its text equivalent. This UDF (user defined function) for Excel will change or tra
Filter Data in Excel to Show Only the Top X Percent of that Data Set - AutoFilter
- This Excel macro filters a set of data in Excel and displays only the top X percent of that data. This means the highes
Replace Formulas with Values (For The Entire Workbook)
- This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to
Get the Number(s) Out of a Cell that Contains Both Text and Numbers in Excel - UDF
- This free Excel UDF allows you to get the numbers out of a cell that contain both text and numbers. This is a great fun
Format Cells in The Number (Numerical) Number Format in Excel
- This free Excel macro formats selected cells in the Number or Numerical number format in Excel. This means that the cel

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?




Hi,

I am looking for a macro or formula to convert GB Latitude longitude Ref to 6 digit OS grid Ref. I have 20 + Latitude longitude Ref to convert at a time,

Thank you for looking at this,

Cheers

WCE

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 "N48-38-46.5" and "E088-19-14.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 co-ordinates 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 co-ordinates 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 co-ordinates are displayed like this but the longitude stays as entered?

Thanks


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


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 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 one-cell-wide 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 comma-delimited 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 (forty-six 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/180-lon1*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