Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting Lat/Lon coordinates to Decimal
I have an Excel spreadsheet with Lat/Lon coordinates in an unusual format.
The Degrees, Minutes, Seconds are merged together as text, without spaces. They're in two columns, like this: Lat, Lon 390757, 0902243 These numbers are the equivalent of 39° 07' 57", 90° 22' 43". I want to convert these columns to their decimal equivalents, like this: 39.1325°, 90.378611°. I have Excel 2002. Unfortunately, I'm clueless with Visual Basic. Macros I can handle. Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting Lat/Lon coordinates to Decimal
Need to know what general format these are in. For example, latitude can be
expressed as -90 to 90 degrees, or 0 to 180 degrees. Longitude can be -180 to 180 degrees or 0 to 360 degrees. And there are other formats as well. Can either the lat or long parts have minus (-) signs? Eric "Cheese" wrote: I have an Excel spreadsheet with Lat/Lon coordinates in an unusual format. The Degrees, Minutes, Seconds are merged together as text, without spaces. They're in two columns, like this: Lat, Lon 390757, 0902243 These numbers are the equivalent of 39° 07' 57", 90° 22' 43". I want to convert these columns to their decimal equivalents, like this: 39.1325°, 90.378611°. I have Excel 2002. Unfortunately, I'm clueless with Visual Basic. Macros I can handle. Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting Lat/Lon coordinates to Decimal
Ah yes, good point.
All coordinates are within the United States, which means the latitude is always N (+) and longitude is always W (-). The minus sign in my decimal example was added by me. I thought maybe this could be added later, if easier. "EricG" wrote: Need to know what general format these are in. For example, latitude can be expressed as -90 to 90 degrees, or 0 to 180 degrees. Longitude can be -180 to 180 degrees or 0 to 360 degrees. And there are other formats as well. Can either the lat or long parts have minus (-) signs? Eric "Cheese" wrote: I have an Excel spreadsheet with Lat/Lon coordinates in an unusual format. The Degrees, Minutes, Seconds are merged together as text, without spaces. They're in two columns, like this: Lat, Lon 390757, 0902243 These numbers are the equivalent of 39° 07' 57", 90° 22' 43". I want to convert these columns to their decimal equivalents, like this: 39.1325°, 90.378611°. I have Excel 2002. Unfortunately, I'm clueless with Visual Basic. Macros I can handle. Thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting Lat/Lon coordinates to Decimal
I made these assumptions:
1. Latitude can be -90 to 90 degrees 2. Longitude can be -180 to 180 degrees 3. Your data are in Column A, starting at Row 3 Put this formula in Column B: =IF(LEFT(A3,1)="-",LEFT(A3,3),LEFT(A3,2))+IF(LEFT(A3,1)="-",MID(A3,4,2),MID(A3,3,2))/60+IF(LEFT(A3,1)="-",MID(A3,6,2),MID(A3,5,2))/3600 Put this formula in Column C: =IF(MID(A3,FIND(",",A3,1)+2,1)="-",MID(A3,FIND(",",A3,1)+2,4),MID(A3,FIND(",",A3,1) +2,3))+IF(MID(A3,FIND(",",A3,1)+2,1)="-",MID(A3,FIND(",",A3,1)+6,2),MID(A3,FIND(",",A3,1) +5,2))/60+IF(MID(A3,FIND(",",A3,1)+2,1)="-",MID(A3,FIND(",",A3,1)+8,2),MID(A3,FIND(",",A3,1) +7,2))/3600 Then drag the formula down however many rows of data you have to convert. That should get you what you want. Here's what I get in my test cases: Lat/Long String Latitude Longitude Decimal Decimal 390757, 0902243 39.13250 90.37861111 -390757, 0902243 -38.86750 90.37861111 390757, -0902243 39.13250 -89.62138889 -390757, -0902243 -38.86750 -89.62138889 HTH, Eric "Cheese" wrote: Ah yes, good point. All coordinates are within the United States, which means the latitude is always N (+) and longitude is always W (-). The minus sign in my decimal example was added by me. I thought maybe this could be added later, if easier. "EricG" wrote: Need to know what general format these are in. For example, latitude can be expressed as -90 to 90 degrees, or 0 to 180 degrees. Longitude can be -180 to 180 degrees or 0 to 360 degrees. And there are other formats as well. Can either the lat or long parts have minus (-) signs? Eric "Cheese" wrote: I have an Excel spreadsheet with Lat/Lon coordinates in an unusual format. The Degrees, Minutes, Seconds are merged together as text, without spaces. They're in two columns, like this: Lat, Lon 390757, 0902243 These numbers are the equivalent of 39° 07' 57", 90° 22' 43". I want to convert these columns to their decimal equivalents, like this: 39.1325°, 90.378611°. I have Excel 2002. Unfortunately, I'm clueless with Visual Basic. Macros I can handle. Thanks! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting Lat/Lon coordinates to Decimal
As Chip ( http://www.cpearson.com/Excel/latlong.aspx )
points out lat/long can be represented using either time format or a custom format I will show how to get 39º07'57" I will let you look at his site to see how to reformat to get 39º07'57" With 390757 in A1 use these formulas in B1: =INT(A1/10000) to get 39 in C1 use =MOD(INT(A1/100),100) to get 7 (do not worry about leading zero) in D1 use =MOD(A1,100) to get 57 Now it is tempting to use =TIME(B1,C1,D1) but TIME always makes the hours less than 24 so we would get 15:07:57 Rather use =(B1/24)+(C1/(24*60))+(D1/(24*60*60)) and format the cell with [hh]:mm:ss I know the formula could be simplified but I wanted to show how it works: since Excel stores time as a fraction of a day we must make all three values fractions of a day When you have it working you can do in one formula =(INT(A1/10000)/24)+(MOD(INT(A1/100),100)/(24*60))+(MOD(A1,100)/(24*60*60)) Bad lightening storm, must sign off best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Cheese" wrote in message ... I have an Excel spreadsheet with Lat/Lon coordinates in an unusual format. The Degrees, Minutes, Seconds are merged together as text, without spaces. They're in two columns, like this: Lat, Lon 390757, 0902243 These numbers are the equivalent of 39° 07' 57", 90° 22' 43". I want to convert these columns to their decimal equivalents, like this: 39.1325°, 90.378611°. I have Excel 2002. Unfortunately, I'm clueless with Visual Basic. Macros I can handle. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting Hours to Decimal | Excel Discussion (Misc queries) | |||
Converting time to decimal | Excel Worksheet Functions | |||
Batch converting CSV files from comma-decimal to period-decimal | Excel Discussion (Misc queries) | |||
Converting 2-place decimal value to floating point decimal number with leading zero | Excel Discussion (Misc queries) | |||
Converting to Decimal degree's | Excel Worksheet Functions |