Dan,

If you don't mind writing a bit of VBA, copy and paste the following into a
module.
Then enter EvalUTM(A1), where A1 is the cell that contains the UTM string to
be decoded.

EvalUTM returns a two element array. So you need to enter the EvalUTM(A1)
two cells, say B1 and B2, highlight the both cells and press Ctl-Shift-Enter
to make it an array formula.

hope this helps.

/m

ps. I had to write a TrimWhiteSpace() function because your string seemed to
contain some strange, invisible, Unicode characters.


Function EvalUTM(coord As String) As Variant
Dim b As Variant, a As String, i As Integer

a = Replace(coord, "°", "+")
a = Replace(a, "'", "/60 +")
a = Replace(a, """", "/3600")
a = Replace(a, "E", "")
b = Split(a, "N")
For i = 0 To UBound(b)
b(i) = Application.Evaluate(TrimWhiteSpace(CStr(b(i))))
Next
EvalUTM = Application.Transpose(b)
End Function

Function TrimWhiteSpace(pStr As String)
Dim a As String, b As Integer, i As Integer
a = pStr
For i = 1 To Len(a)
b = Asc(Left(a, 1))
If b <= 32 Or b = 127 Then a = Right(a, Len(a) - 1)
Next
TrimWhiteSpace = a
End Function


"Dan" wrote in message ...
Hi there,

I do have a string in a cell that looks like this (this is UTM ED50 format):

46° 34' 21.09" N 8° 24' 54.28" E


I need to convert this string to Latitude and Longitude. The calculation
method is pretty simple. In the example above, its
46 + 34/60 + 21.09/3600 = 46.572525 (Lattitude)
8 + 24/60 + 54.28/3600 = 8.415077778 (Longitude)

So, the calculation as such is not very complicated.
What drives me nuts is to get the numbers out of the long ED50 string. I
managed this with a LOT of SEARCH() and MID() functions, but it's just
awfull !!.

Latitude
=MID($K2,1,SEARCH("°",$K2)-1)+(MID($K2,SEARCH("°",$K2)+1,(SEARCH("'",$K2)-SE
ARCH("°",$K2))-1))/60+(MID($K2,SEARCH("'",$K2)+1,(SEARCH("""",$K2)-SEARCH("'
",$K2))-1)/3600)
Longitude
=MID(MID($K2,22,15),1,SEARCH("°",MID($K2,22,15),1)-1)+MID(MID($K2,22,15),SEA
RCH("°",MID($K2,22,15),1)+1,SEARCH("'",MID($K2,22, 15),1)-SEARCH("°",MID($K2,
22,15),1)-1)/60+MID(MID($K2,22,15),SEARCH("'",MID($K2,22,15),1) +1,SEARCH("""
",MID($K2,22,15),1)-SEARCH("'",MID($K2,22,15),1)-1)/3600

Anyone knows a simpler way?

Thanks

Dan