convert lat/longs

[batorizer]

Hey guys. I have a little problem I've been trying to wrap my head around. I'm not an excel expert, so I thought I'd come look for some help. I'm trying to create a spreadsheet that will convert coordinates from DDMMSS.00 to DDMM.000. I found another thread that discusses this same issue, but I was unable to make it work for me. I'm attaching a sample spreadsheet that basically shows what I'm trying to do. Let me know if there's any more info ya'll need.

Chris

[shg]

Welcome to the forum.

Wouldn't plain decimal degrees be easier if you're doing any downstream calculation?

[shg]

Never mind ...

=INT(LEFT(A2, LEN(A2)-1)/100) + MOD(LEFT(A2, LEN(A2)-1), 100) / 60

For latitudes, format as 0.000"N";0.000"S"

For longitudes, 0.000"W";0.000"E"

[batorizer]

Thank you sir, the formula works perfectly and you've helped me avoid some writers cramp.

Luckily, I've got software that does all the calculations. As for this spreadsheet, it's one of the final steps in the process and is used mainly for documentation.

[shg]

You’re welcome. Would you please mark the thread as Solved?

Click the Edit button on your first post in the thread

Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes

[batorizer]

I've got another quick question for ya. I've entered the cell format like you have it, but I can only get N or W to show. Is there any way to make the hemisphere in the cell format of the computed cell dependant on which hemisphere I've used originally?

[shg]

Ah, yes: = IF(OR(RIGHT(A2)={"N","E"}), 1, -1) * (INT(LEFT(A2, LEN(A2) - 1)/100) + MOD(LEFT(A2, LEN(A2) - 1), 100) / 60)

[batorizer]

Hmm. I wasn't able to get that to work, but I did come up with something more rudimentary:

Please Login or Register  to view this content.

When I added the IF statment, the cell formatting was basically ignored and many decimal places were shown, rather than the three I need. So, I added the rounding. My problem is that if the third decimal place is a zero, excel omits it and I can't figure out how to make it show the last zero. Any ideas?

[shg]

Here's the result of applying my formula to your data:

Please Login or Register  to view this content.

Did you remember to apply the formatting shown in Post#3?

[batorizer]

Well, I thought I had everything right on the spreadsheet I was originally experimenting with, but I must've had something wrong. Since I started a new file, all has been well and working as it should. I do appreciate you taking the time to help. Thanks again.