Here is an attempt using Regular Expressions if you want to try that. I'm
not familiar with ED50. For your return example of Longitude 8.415077778,
do you want to place a "-" in front of it to indicate "East" Longitude? I
left the N & E as is. I used 1 multiplication and 1 Division, instead of 2
divisions. If you are doing many of these, you would want to pull out the
RegExp as a global object so that you do not keep creating the object with
each call.
(As a side note, I could not find any information on converting the data to
WGS 84 if that is what you wanted also.)
Again, just an idea I was playing with. There are many other ways to do
this. RegExp tend to be a little slow, but there are no programming loops
either.

Debug.Print ED50_Clean(Range("A1"))

N 46.572525 E 8.41507777777778

Function ED50_Clean(s As String) As String
'// = = = = = = = = = = = = = = = = = = = = = = = = =
'// Requires VBA Library Reference:
'// Microsoft VBScript Regular Expressions 5.5
'// = = = = = = = = = = = = = = = = = = = = = = = = =

Dim v As Variant
With CreateObject("VBScript.RegExp")
.Global = True
.IgnoreCase = True
.Pattern = "([0-9.]|[NESW])+"
Set v = .Execute(s)
' d + (60*m + s)/3600
If v.Count = 8 Then
ED50_Clean = _
v(3) & Space(1) & v(0) + (60 * v(1) + v(2)) / 3600 & Space(1) &
v(7) & Space(1) & v(4) + (60 * v(5) + v(6)) / 3600
Else
ED50_Clean = "Error in String"
End If
End With
End Function


HTH
Dana DeLouis


"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