# Convert maidenhead grid square to lat/long in Excel?

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?

• 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.

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

• 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. – Chris K8NVH Nov 25 '18 at 17:12

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 -*-

__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

(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