3
$\begingroup$

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?

$\endgroup$
  • $\begingroup$ 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. $\endgroup$ – rclocher3 Apr 27 '16 at 16:40
  • $\begingroup$ Possibly related answer here. You could take apart the Maidenhead string and add it up. $\endgroup$ – tomnexus Apr 28 '16 at 5:52
4
$\begingroup$

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.

$\endgroup$
1
$\begingroup$

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

$\endgroup$
  • 1
    $\begingroup$ Hi Bert, welcome to StackExchange! Is this a VBA function? Could you please add a few lines describing how to get this into Excel and read the appropriate cells? Regardless, your logic is clear and this should be straightforward to port to a language a reader may be more familiar with. Thank you for posting this. $\endgroup$ – Chris K8NVH Nov 25 '18 at 17:12
0
$\begingroup$

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
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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