Latitude & Longitude processing help please

Originally Posted by shg

Welcome to the forum.

Post a workbook that illustrates the possibilities.

Ok here is the example document... I've noticed that the issue with the zero's that I've described earlier does not exist not that I've brought the data home with me (maybe a format setting on the other copy of excel?), so that part of the problem at least is out of the picture... the lat is always in the format DDMMSS N/S and the lon is always in the format DDDMMSS E/W with W/S needing to be negative. I'm not 100% sure that all of the E/W/N/S are capital or not, but at least one of the problems is off the table.

Having posted this I think I've overlooked the conversion from minutes/seconds to decimal. Maybe further examples can be given.

With 1083015W in A1 try this in B1:
=IF(LEN(A1)=8,LEFT(A1,LEN(A1)-1)/10000,LEFT(A1,LEN(A1)-1)/100)*IF(ISERROR(FIND(RIGHT(A1,1),"WS")),1,-1)

Format the answer to 4 decimal places. (-108.3015)

I'm assuming:
- West and South must be negative. If I am wrong, change the "WS" text accordingly.
- the last letter is always upper case.
- if the format is DDDMM, the SS is zero.

Excel 2007 has a nicer formula to test for errors, but I only have the 2003 version.

hth
Mike

I'll go ahead and give this a try, but I think there are a couple of problems that might arise with the new info in mind. First, all of the lat are 7 digits including the letter and all of the lon are 8, so the determining code might get tripped up? Second, I'm not sure if the letter is always upper case... I'm in the process of scanning through the full table to see, but there are a lot so it may take a little while. And last, with the new data format that I've found since I've come home, if there are any double zero's its usually in the DD part of the coordinate rather than the SS part. Should this still work? Or is a new formula required?

Thanks again,
Greg

with a test longitude value 1222659W in A1, try this in B1:
=(LEFT(A1,3)+MID(A1,4,2)/60+MID(A1,6,2)/3600)*IF(ISERROR(FIND(upper(RIGHT(A1,1)),"WS")),1,-1)
Gives -122.4497.

hth

Regards
Mike

And for latitude (length 7 characters), eg 381326N in A7:

=(LEFT(A7,2)+MID(A7,3,2)/60+MID(A7,3,2)/3600)*IF(ISERROR(FIND(RIGHT(A7,1),"WS")),1,-1)

gives 38.2203

hth
Mike

Well it almost works like a charm... nothing is wrong with the formula, but the data in this case again. Some of the records are missing a lat or long (or both) coordinate, which produces an unusable entry into the formula field, causing an error when I'm trying to import the resultant table into the mapping program I'm using. What changes would be needed to the formulas already given to cause the output to be a zero if there is nothing there?

Thanks a lot,
Greg

Try:
=IF(A1<>"",(LEFT(A1,3)+MID(A1,4,2)/60+MID(A1,6,2)/3600)*IF(ISERROR(FIND(RIGHT(A1,1),"WS")),1,-1),0)
and
=IF(A7<>"",(LEFT(A7,2)+MID(A7,3,2)/60+MID(A7,3,2)/3600)*IF(ISERROR(FIND(RIGHT(A7,1),"WS")),1,-1),0)

I've added an if statement that checks if the cell is not empty, calculates the formula if not empty, or returns a zero if it is empty.

Regards
Mike

@martin - awesomely simple, thanks for sharing!

PS - uh oh, doesn't work for 0952836W?

Regards
Mike

Thank you both for your efforts. Thanks to you, I've been able to quickly convert all of the records in a matter of seconds rather than days. In the end I went with Mike's solution, even though it is a little more complicated, as even Martin's corrected solution appeared not to work for every coordinate. Anyway, thank you again.

Thanks,
Greg