I have a list of maidenhead grid squares in an Excel sheet that I want to convert to latitude and longitude. I want to keep it as simple as possible. Does anyone know of a formula to convert from maidenhead to lat/long?
Assuming for the sake of precision that the particular point in the grid square that you want the exact latitude and longitude for is the midpoint of the 6-character subsquare, this can be done readily with Excel formulas.
If the 6-character grid square data is in cell A1, in a format similar to AA00aa (i.e. upper-case, then digits, then lower-case), the formula for the latitude (based directly on the Python code posted previously) is:
=(CODE(MID(A1,2,1))-65)*10 + VALUE(MID(A1,4,1)) + (CODE(MID(A1,6,1))-97)/24 + 1/48 - 90
and the formula for the longitude is
=(CODE(MID(A1,1,1))-65)*20 + VALUE(MID(A1,3,1))*2 + (CODE(MID(A1,5,1))-97)/12 + 1/24 - 180
If you want the latitude and longitude of the southwest corner of the subsquare, just leave out the + 1/48 and + 1/24 terms. Add error-checking, upper- and lower-case conversion, conversion of four-character squares to six-character by adding 'mm', and other embellishments as you see fit.
Function FromMaidenhead(m As String) As Variant Dim lat As Double, lon As Double
lat = (Asc(Mid(m, 2, 1)) - 65) * 10 + Val(Mid(m, 4, 1)) + (Asc(Mid(m, 6, 1)) - 97) / 24 + 1 / 48 - 90 lon = (Asc(Mid(m, 1, 1)) - 65) * 20 + Val(Mid(m, 3, 1)) * 2 + (Asc(Mid(m, 5, 1)) - 97) / 12 + 1 / 24 - 180
FromMaidenhead = Format(lat, "00.000000" & "° ") & Format(lon, "000.000000" & "° ")
This seems to be impossible doing with a Macro correctly.
Even when you take the link provided by @tomnexus in the comments and reverse the process, you will not get a precise location.
Reason is: a Maidenhead locator is a square, and therefore consists of my combinations of lon/lat. Therefore a Macro cannot result in a single lon-lat.
However, I am sure that you can come up with a script/macro with 4 results, which represent ranges:
lon = (lon_start) to (lon_end)
lat = (lat_start) to (lat_end)
Can you please clarify what you are looking for ?
I am not an excel person, but I know a bit of Python 3.x, so here is a script which actually translates Maidenhead to a range of lon/lat... use at your discretion, no warrantees made
# -*- coding: utf-8 -*- #maiden head to lon/lat __MH__ = 'HK34wh' def GetLon(ONE, THREE, FIVE): StrStartLon = '' StrEndLon = '' Field = ((ord(ONE.lower()) - 97) * 20) Square = int(THREE) * 2 SubSquareLow = (ord(FIVE.lower()) - 97) * (2/24) SubSquareHigh = SubSquareLow + (2/24) StrStartLon = str(Field + Square + SubSquareLow - 180 ) StrEndLon = str(Field + Square + SubSquareHigh - 180 ) return StrStartLon, StrEndLon def GetLat(TWO, FOUR, SIX): StrStartLat = '' StrEndLat = '' Field = ((ord(TWO.lower()) - 97) * 10) Square = int(FOUR) SubSquareLow = (ord(SIX.lower()) - 97) * (1/24) SubSquareHigh = SubSquareLow + (1/24) StrStartLat = str(Field + Square + SubSquareLow - 90) StrEndLat = str(Field + Square + SubSquareHigh - 90) return StrStartLat, StrEndLat def main(strMaidenHead = __MH__): if len(strMaidenHead) < 6: strMaidenHead = __MH__ ONE = strMaidenHead[0:1] TWO = strMaidenHead[1:2] THREE = strMaidenHead[2:3] FOUR = strMaidenHead[3:4] FIVE = strMaidenHead[4:5] SIX = strMaidenHead[5:6] (strStartLon, strEndLon) = GetLon(ONE, THREE, FIVE) (strStartLat, strEndLat) = GetLat(TWO, FOUR, SIX) print ('Start Lon = ' + strStartLon) print ('End Lon = ' + strEndLon) print () print ('Start Lat = ' + strStartLat) print ('End Lat = ' + strEndLat) return strStartLon, strEndLon, strStartLat, strEndLat #BEGIN if __name__ == '__main__': main () sys.exit ('end of script: '+ os.path.basename(__file__) + os.linesep + 'by: Edwin van Mierlo') #END