Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 220
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 220
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,104
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting Hours to Decimal Helen Excel Discussion (Misc queries) 7 May 24th 10 05:37 PM
Converting time to decimal NoodNutt Excel Worksheet Functions 4 April 18th 08 06:16 AM
Batch converting CSV files from comma-decimal to period-decimal Nodles Excel Discussion (Misc queries) 3 July 5th 06 06:57 PM
Converting 2-place decimal value to floating point decimal number with leading zero Kermit Piper Excel Discussion (Misc queries) 3 March 18th 06 07:20 PM
Converting to Decimal degree's Gary''s Student Excel Worksheet Functions 0 February 4th 06 03:04 PM


All times are GMT +1. The time now is 11:03 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"