Latitude/ Longitude Formatting

[reganshaw]

Hi All - It's been a while - hope one of you can help.

I have latitude & longitude information in a text string that I am trying to convert to decimal format.

In cell A1 the Latitude value is 0434010N
In cell B1 the Longitude value is 0792201W

In both of the above;
Characters 1 - 3 represent Degrees
Characters 4 - 5 represent Minutes
Characters 6 - 7 represent Seconds
Character 8 is "N" for Latitude and "W" for Longitude

In cell C1 I want the Latitude to display as 43.669364, and in D1 the Longitude to display as -79.367098

Any suggestions?

Thanks in advance.

 

[shg]

	a​	b​	c​
1​	0434010n	43.669444​	b1: =sumproduct(--mid(a1, {1,4,6}, {3,2,2}) * {3600,60,1}/3600) * if(or(right(a1) = {"w","s"}), -1, 1)
2​	0792201w	-79.366944​

 

[Tetra201]

Or this:

=TEXT(LEFT(A1,7),"#0\:00\:00")*IF(OR(RIGHT(A1)={"S","W"}),-24,24)

 

[reganshaw]

Thank you both! Both are perfect and do exactly what I need!
SHG, if it isn't too much to ask, can you explain what is happening between the { }'s in your solution? I've not seen this before.