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



Convert Latitude And Longitude To Decimal Degrees

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

View Answers     

Similar Excel Tutorials

Round to a Specific Decimal for Any Number in Excel
Round each number to a specific decimal number. This works for numbers that don't already have a decimal and ones t ...
Change Minutes from a Decimal to a Percentage of an Hour
Change minutes stored as a decimal into a fraction of an hour. For instance, if time is stored as 8.15, 8 hours and ...
Easily Enter Fractions into Excel
This is a very simple trick to enter fractions quickly without having to manually format anything. This allows you ...
Get the Decimal Part of a Number in Excel
How to get the decimal part of a number without any rounding in Excel. This tip allows you to simply remove the who ...

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.


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 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 a spreadsheet that is supposed to contain longitude and latitude but
the fields are in number format so a mapping program does not recognize the
data. Does anyone know an easy way to format the numbers in lat/long?



Dear all,

I have a large global dataset of 2160 rows and 4320 columns in Excel 2007. My aim is to convert this dataset to a coarser resolution of 360 rows and 720 columns, by taking averages of blocks of the current fine resolution data.

As an aside, the data are presented as a grid, currently at 0.0833333 degrees spatial resolution, whereby the 1st row represents the North Pole, and the 2160th row represents the South Pole. Similarly, the 1st column represents 180 degrees West and the 4320th column represents 180 degrees East. I hope to convert the data to 0.5 degrees (360 rows and 720 columns) so that it is consistant with other datasets I have.

As the dataset is geographical (latitude/longitude), the data need to be averaged in two dimensions (i.e. in blocks, rather than just along a row or down a column). I should also point out that the dataset has no row or column names - each cell used contains data values.

How would I go about doing this in Excel? I'm unfamiliar with VBA macros, so if your answer involves this, could you please help me out with it?!

Many thanks for your help,

smurray444


I have a list of cities w/ latitude and longitude that I have to find the
distance between any two of the cities. The formula I was given is this.....
69*(difference in latitudes)2 + (difference in longitudes)2
VLOOKUP is used for the differences and needs True or False.
Ofcourse I have just started excel so this is really aggrivating.

--
Ladi



Hi,

I'm very new to using Excel for programming etc, so there may well be an easy fix for my problem.

I'm using excel to try and sort by distance a list of wind farm locations from a location i put in.

This should be relitavely straight forward, however there are a few elements that make it more complicated.

I am getting a list of wind farm locations from the internet, the BWEA site. The latitude and longitude data copied over is in text. I'm using Excel '07 but it still requires me to butcher the numbers out of it with several columns of REPLACE formulas, it isn't elegant and may well be wrong, so a better way of doing this would help a lot.

The second and harder problem is that I have to first convert the latitude and lonitude values into OSGB grid references. So far i have found the OS maps excel converter, but it only converts one value at a time. I need something that will convert all of them at once (i don't want to go through and individually type in each distance).

After that it is possible to use basic trigonometry to find the distance between them and any grid reference i put in.

Cheers for any help you can give

Tom