I am in need of some help in converting latitude and longitude position data within an excel spreadsheet.
The latitude and longitude data is gathered from a SQL database and it shows up in my excel spreadsheet in the following formats:
Longitude is DDDMM.mm
where DDD is degrees of longitude, MM is minutes of longitude, and mm is decimal minutes of longitude
Example: 12151.57 which equals 121 degrees 51.57 minutes of longitude.
Latitude is similar and is in the form of DDMM.mm
where DD is degrees of latitude, MM is minutes of latitude, and mm is decimal minutes of latitude
Example: 3630.00 which equals 36 degrees 30.00 minutes of longitude.
Degrees and minutes are sandwiched together. I don't know how to seperate the minutes away from the degrees numbers, so I can divide the minutes by 60 to get decimal degrees.
I am dealing with tens of thousands of these lat 7 Lon positions within 2 excel columns. One column has the latitudes and one column has the longitudes. I need a formula that would convert the DDDMM.mm into DDD.ddd for longitude and convert DDMM.mm into DD.dd for latitude, where D is degrees and d is decimal degrees.
Examples of the conversions I need:
12145.00 needs to convert into 121.75
3630.00 needs to convert into 36.5
Mapping and contouring software can't handle minute or decimal minutes. They need degrees and decimal degrees.
I think there is probably a somewhat simple formula for this conversion to decimal degrees. I just can't seem to figure it out. I am also new to excel so I could use help in exactly how to write the formula within excel that would act upon the column of position data.
Thanks for any help or advise.
Ken