We needed to convert a large number of ordnance survey grid references (NGR) into national X Y coordinates. An OS grid reference will take the format SW123123.

The 'letter' part needed converting to the relevent X Y coordinate for that part of the country - the X coordinate for SW is 1, the Y coordinate is 0. The 'digit' part needed splitting in half - the first 3 digits are the X coordinate, the second 3 digits are the Y coordinate - so X = 123, Y = 123. The X & Y from the letter part then needed combining with the relevent X and Y from the digit part.

To further complicate matters, the received NGRs had 2, 4, 6, 8, 10 or 12 digits. The conversion needed to able to split the digits into the X and Y halves irrespective of how many digits there were, and then standardise them by adding zero's so that each X and Y had 6 digits in total - ie 112300 012300.

Another column was then required to provide a count of how many digits were provided in the first place so that we can guage the precision of the grid reference.

I ended up using a number of extended formulas making use of Excels Vlookup, Left, Right, Len, Concatenate and If Functions. The result seemed to do the job.


Download: NGR.xls