how can i convert degrees/minutes/seconds to decimal degrees?

[russkris]

Hello,

For work we have a spreadsheet that gets uploaded into a database, how ever this year they wont different information regarding the GPS Locations

For example, last year I formatted the GPS as Degrees, Minutes, Seconds
43 04 27.1 147 00 59.9

This was fine, however this year they wont the GPS in Decimal Degrees (GDA94)

With a 20,000 row spreadsheet this is going to be horrible.

Attached you will see the spreadsheet with most of the rows deleted.

Column E and F are the columns that need some help.

in the first 3 rows(3-5) you will see how it was last year however when you pop that into.. say Google Earth, it sends me to the wrong end of the world, only because you I can't add a -(minus) but in rows 6-10 it sends me to the right part of the world.

Now is there an easier way to convert the first 3 rows from 43 XX XX.X to -43 XX XX.X and then into decimal degrees?

Many thanks in advance for any help you can offer.

[MarvinP]

Hi russ,

Looking at your attached file, do you have Lat and Long reversed in E2 and F2?

Also can you do two examples by hand and give us a correct answer. That would really help us check our formulas.

Could you convert 4 different points for us, using N, S, E, W in your examples.

[russkris]

Hi Marvin,

Yes, good pick up. That was a test.. lol j/ks


Edited the spreadsheet and now the Lat/Long are in the right place.

Not sure how to create the four points you need.

[Crooza]

Use text to columns using space as your delimiter to convert to S, Degree, minutes and seconds then simply run a formula = degrees + minutes / 60 + seconds /3600 to convert to decimal (multiple this by -1 to convert to negative)

I just did it in 2 minutes on your example sheet. If you don't follow this let me know

[Crooza]

An Easting will always be positive (West negative). A Northing will be positive (South negative) so the E will be fine as is but the South will require a negative. Did you try my suggestion above?

[russkris]

Hi Crooza,

Sorry I missed your post. Many apologies.

You will have to excuse me, I am a noob with forumlas.

Would you mind, if it is not to much trouble, posting the forumla?

[Crooza]

gps(1) decimal degrees.xlsx

Try this. The formula is on sheet 2

I copied the column from your original sheet and pasted it as a single column in cell A1 of sheet 2.
Then I went to the data tab and selected convert text to columns, and delimited by space. This will split the single cells into columns. You'll get a slight misalignmnet with your columns due to you having an S in some and not in others. I manually realigned this but I'm assuming in your real data all your data is the same format. I then put the formula in to cell E1 and copied down. You could copy and paste as values back into sheet 1 if you wish

[russkris]

Hi Crooza,

So what I need to do is create extra columns via the data tab(so that will be F,G,H) then add the formula to Column I to get the DD Lat and the same for the Long?

[Crooza]

That will work or do as I did and do it all in a clean tab (sheet 2) and copy just the answer column back to the original (but copy and paste as values not formulas when you do it)

Send me the full file if you want and I'll sort it out providing it's not too big

[russkris]

20,000 rows mate.. lol

I should be able to do it. Shame it couldnt be done through a marco

[Crooza]

You could do it with a macro but it would take me longer to write the macro than it would to manually do the change using the above method. Happy to help if you want to send the file through

[russkris]

When it hits a blank cell it returns zero. Not sure how to get rid of this.

[russkris]

Thanks heaps for your help. I try and use these forums to learn as I go.


What do you think of this?

For the column next to the Long

=-1*(SUBSTITUTE(SUBSTITUTE(A2,"S ","")," ",":"))*24

For the column next to the Lat

=(SUBSTITUTE(SUBSTITUTE(F2,"E ","")," ",":"))*24

Yet to test in bulk but it seem Google Earth likes it

[Crooza]

Good thinking with the time format. seems to work well

if you want a blank cell when you hit a blank cell the wrap your formula in an if like this

=if(a2="","",-1*(SUBSTITUTE(SUBSTITUTE(A2,"S ","")," ",":"))*24)

[russkris]

I would like to take full credit but I can't.

Cheers for the help.