I have a list of maidenhead grid squares in an excel sheet that I want to covert to lat/long. I want to keep it as simple as possible. Does anyone know of a formula to convert from maidenhead to lat/long? thanks

  • A web search makes it look as though your task is too complicated for a formula. A macro could probably be written to do it. – rclocher3 Apr 27 '16 at 16:40
  • Possibly related answer here. You could take apart the Maidenhead string and add it up. – tomnexus Apr 28 '16 at 5:52

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.

  • brilliant thanks – QST Sep 24 '16 at 17:48

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

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.