Convert maidenhead grid square to lat/long in Excel?

Question

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?

Answer 1

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.

Answer 2

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 ?

[edit 04-May]

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

Answer 3

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" & "° ")

End Function