Converting Lat/Lon coordinates to Decimal

[Cheese]

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!

 

[EricG]

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]

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]

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

 

[Bernard Liengme]

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