| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Simple way to convert UTM ED50 coordinates to decimal coordinates?
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 |
|
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
|
Simple way to convert UTM ED50 coordinates to decimal coordinates?
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 insertcolumns Select column K and do datatext to columnsfixed width, make sure all you values are there, click next and finish, now select column K and column O and do editreplace, 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) "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 |
|
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
|
Simple way to convert UTM ED50 coordinates to decimal coordinates?
Hi Dan..........
Data Text to columns Fixed..........then set and/or create your coulum breakes where you want them.......... Vaya con Dios, Chuck, CABGx3 "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 |
|
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
|
Simple way to convert UTM ED50 coordinates to decimal coordinates?
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 "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 |
|
#5
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
|
Simple way to convert UTM ED50 coordinates to decimal coordinates?
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 |
|
#6
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
|
Simple way to convert UTM ED50 coordinates to decimal coordinates?
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 |
|
#7
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
|
Simple way to convert UTM ED50 coordinates to decimal coordinates?
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 "Peo Sjoblom" wrote in message ... 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 insertcolumns Select column K and do datatext to columnsfixed width, make sure all you values are there, click next and finish, now select column K and column O and do editreplace, 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) "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 |
|
#8
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
|
Simple way to convert UTM ED50 coordinates to decimal coordinates?
hello mojo,
cool! I am going to try this evening. (now I'm off to some motorcycling :-) thanks a lot Dan "mojo" wrote in message om... 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 |
|
#9
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
|
Simple way to convert UTM ED50 coordinates to decimal coordinates?
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Coordinates | Excel Worksheet Functions | |||
| Converting Lat/Lon coordinates to Decimal | Excel Discussion (Misc queries) | |||
| How do I convert Easting/ Northing GPS coordinates into Lat/Long? | Excel Discussion (Misc queries) | |||
| convert x/y coordinates to a shape file to use in Cad? | Excel Discussion (Misc queries) | |||
| Converting MouseDown Coordinates to Chart Point Coordinates | Excel Programming | |||
![Dan[_38_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
