Simple way to convert UTM ED50 coordinates to decimal coordinates?

[Dan]

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

 

[Peo Sjoblom]

This worked for me using your example, assume the values are in column K,
insert
8 empty columns to the right of K, that is select columns L to S and do
insert>columns
Select column K and do data>text to columns>fixed width, make sure all you
values are there,
click next and finish, now select column K and column O and do edit>replace,
place the cursor in the
find what box and hold down alt while typing 0176 on the numpad, release
Alt, leave replace with empty and select replace all
now select columns L and P, do the same but put an apostrophe in the find
what box, repeat once again for columns
M and Q but put a quotation mark in the find what box. That would give you
numbers in each column except for the N and the E i columns N and R

Now in a cell get the Latitude as follows

=K2+L2/60+M2/3600

copy down

for Longitude

=O2+P2/60+Q2/3600

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[CLR]

Hi Dan..........

Data > Text to columns > Fixed..........then set and/or create your coulum
breakes where you want them..........

Vaya con Dios,
Chuck, CABGx3

 

[AlfD]

Hi Peo!

It's quite interesting to do a variant on your routine. Replace the '
" etc with, say, #. N could be replaced by #0; E by #90 etc i
needed.
Then do the Text to Columns step with # as the separator.

Al

 

[Peo Sjoblom]

There are many ways to skin this cat, I also tried sucessfully to just
split k2 with N as delimiter using text to columns,
then using edit>replace and ° replaced with + ' replaced with /60+ and "
replaced with /3600
then in an adjacent cell put ="="&K2 copy down, then do edit>paste special
as values in place,
and finally replace the equal sign with the equal sign and that would return
a formula

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Karen Wiggins]

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.

/k

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

 

[mojo]

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

 

[mojo]

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]

Thanks Peo and all who responded.
I did try the Text to Columns together with search/replace before.
But what I wanted was a dynamic way, as the table is going to be
built by web queries (see other article "parsing HTML table into
excel. How?". I should have written this at the first place.
there is a VBA approach from mojo that I'd like to try.

thanks a lot

Dan

 

[Dan]

hello mojo,

cool!
I am going to try this evening. (now I'm off to some
motorcycling

thanks a lot

Dan

 

[Dana DeLouis]

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