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]

[table="width:, class:grid"][tr][td="bgcolor:#c0c0c0"][/td][td="bgcolor:#c0c0c0"]

a​

[/td][td="bgcolor:#c0c0c0"]

b​

[/td][td="bgcolor:#c0c0c0"]

c​

[/td][/tr][tr][td="bgcolor:#c0c0c0"]

1​

[/td][td]0434010n[/td][td="bgcolor:#ccffcc"]

43.669444​

[/td][td="bgcolor:#ccffcc"]b1: =sumproduct(--mid(a1, {1,4,6}, {3,2,2}) * {3600,60,1}/3600) * if(or(right(a1) = {"w","s"}), -1, 1)[/td][/tr]
[tr][td="bgcolor:#c0c0c0"]

2​

[/td][td]0792201w[/td][td="bgcolor:#ccffcc"]

-79.366944​

[/td][td][/td][/tr]
[/table]

 

[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.