1

Re: OS Grid Refs and Eastings Northings

This might be in the wrong section of the forum but nowhere else seems suitable...

I've got an excel spreadsheet with OS Grid References of various lengths.  This data needs to be entered into GIS but it has over 3000 records in it so doing it one at a time isn't really possible!  Does anyone know of a tool that will convert these grid references into eastings and northings i.e. NY373827 would become 337300 582700.

-----------------------------------------------------------------------
Steve Hannah - GIS and Data Infrastructure Officer
The Wildlife Information Centre

2 (edited by RobLarge 17-02-2011 17:54:13)

Re: OS Grid Refs and Eastings Northings

There are tools around but probably better to teach yourself to do it in Excel, here's a rough idea how (assuming they are all in the NY 100km square and your grid refs are in column A, starting from A1)

In the first free cell of column B type the following        =LEN(A1)

Then hover your cursor over the bottom right corner of the cell and drag the little + sign that appears downwards until you reach the bottom row of the grid references. That will populate the whole of column B with numbers representing the length of each grid ref.

Now sort the spreadsheet by column B to get all the grid refs of the same length appearing together and handle each block separately.

For the six figure grid refs 8 characters long as in your example (assuming these  start at cell A1)

In the first cell of column C type this  = "3" & MID(A1, 3, 3) & "00"

In the first cell of column D type this  = "5" & Right(A1, 3) & "00"

Select both the cells you have just typed in and drag the + sign down to the end of the six figure refs.

When you have got the hang of that do eight figures by changing the second 3 in the C column expression to a four and reducing the number of zeros at the end to just one. For the D column expression change the 3 to a 4 and reduce the zeros to one.

That should be all the information you need and it should take less time than I have to write this.

Who needs a tool?

By the way, before opening in GIS you should select and edit/copy the C & D columns, then edit/Paste special  them back onto the same location as values (overwriting the formulas you typed) otherwise they may come out as something unexpected in your GIS

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

3

Re: OS Grid Refs and Eastings Northings

I'm not sure its really necessary to teach yourself to do it in excel when there are tools out there that do it for you - there are lots of people who are not happy with excel formulae but need to do this. (Although if you can I would advise doing as Rob says and getting to grips with the formulae as it is useful in other ways.)

If you drop me an email I can send the spreadsheet Matt uses for Cumbria. I took the spreadsheet we used in Kent and made a function version with macros - unfortunately I never got around to adding in all the 100km squares so it only works with TQ and TR at the moment but would be easy to modify. I can email you that as well if you want.

-----------------
Teresa Frost | Wetland Bird Survey National Organiser | BTO
Other hat  | National Forum for Biological Recording Council
(Old hats  | NBN Board, ALERC Board, CBDC, KMBRC)

4

Re: OS Grid Refs and Eastings Northings

There is an excellent Excel spreadsheet that provides all manner of spatial conversions.

http://pbrady.bangor.ac.uk//osgb.xls

It is briefly described, amongst various other related topics, here

http://pbrady.bangor.ac.uk/osgbfaq.htm

Mike Beard
Natural Course Project Officer
Greater Manchester Local Records Centre

5

Re: OS Grid Refs and Eastings Northings

Thanks for the help. 

I'd written an excel formula to do it, but the problem was that I had multiple 100km squares, in varying lengths.  I'd written something that would change the initial number on the eastings/northings based on whether it was NX, NY etc, i.e. for NX247855 it would return 224700 585500 but for NT657998 it would return 365700 699800, but it only worked for 6 figure grid references as the last two digits were a fixed concatenation.

I may try implementing the VB code at a later date, so thanks for that as well.  It's been about 6 years since I've done any VBA coding at all and it's only just beginning to come back to me!

In any case I have something else that works now.  But thanks for the help guys!

-----------------------------------------------------------------------
Steve Hannah - GIS and Data Infrastructure Officer
The Wildlife Information Centre

6 (edited by TeresaF 22-02-2011 14:37:16)

Re: OS Grid Refs and Eastings Northings

Thanks for that link mbeard, I've not seen that before and there are lots of useful functions in there, particularly for tidying grid references, and for getting from GPS to OS Eastings and Northings it looks very useful indeed.

My simple code does two things which that spreadsheet doesn't do, that NBN peeps might find useful for producing distribution maps in Arc/MapInfo - it gives the option of eastings and northings for the centre of the square rather than the SW corner and copes with tetrads.

[Edit 22/2/11 - oops I had seen that spreadsheet before, as I have it saved on my computer from a few years ago! It never hurts to be reminded these things exist.]

-----------------
Teresa Frost | Wetland Bird Survey National Organiser | BTO
Other hat  | National Forum for Biological Recording Council
(Old hats  | NBN Board, ALERC Board, CBDC, KMBRC)

7

Re: OS Grid Refs and Eastings Northings

Hello Steve

If all the above fails then I have an Excel Addin produced by JNCC that converts grid references to Easting and Northings. Let me know and I can send it to you

Best wishes

Graham
NBN Technical Liaison Officer

8

Re: OS Grid Refs and Eastings Northings

Hello all,
I think, we are using the same tool as Graham offered. It converts between Easting/Northing, OS Grid references and Lat/Lon, I can highly recommend it. So far I haven't managed to use it in ArcGIS, which I would like to do. It should be relatively easy, because I think the tool was created with VBA and ArcGIS uses VBA as well. So my question is, if somebody has sucessfully implemented it into ArcGIS?

Best wishes
Wolfgang

9

Re: OS Grid Refs and Eastings Northings

The tool we have now is an access database with a query and a vba function written into it by Mike Weideli.  The advantage of this is we can export it straight away to dbf, and then create a GIS Shapefile from it really quickly.

-----------------------------------------------------------------------
Steve Hannah - GIS and Data Infrastructure Officer
The Wildlife Information Centre

10

Re: OS Grid Refs and Eastings Northings

Does anyone have a tool which could be freely shared which can export grid references straight to shapefile based on polygon squares (like the export to shapefile in Recorder)? There is an Exegesis tool at Kent which can do this, but which cannot be freely shared.

I thought something like the C shapelib library http://shapelib.maptools.org/ or genshapes in spatial ecology http://www.spatialecology.com/gme/genshapes.htm could be used to do this pretty easily, but have never got around to implementing.

Returning to the original problem, I have extended my excel code to work for the whole country,and it can now cope with upper and lower case letters and spaces in the grid reference. It will also give the midpoint and minimum and maximum distances (based on the precision) between a site grid reference and a list of record grid references. This may be of use to people in reporting without having to use GIS? I will stick it up online somewhere as soon as I can and post the link here. I have deleted my earlier post with the VBA code in it as I don't want to put people off what is turning into a useful thread.

Graham, what else can the NBN add-in do besides eastings and northings? Could it be shared via the NBN website?

-----------------
Teresa Frost | Wetland Bird Survey National Organiser | BTO
Other hat  | National Forum for Biological Recording Council
(Old hats  | NBN Board, ALERC Board, CBDC, KMBRC)

11

Re: OS Grid Refs and Eastings Northings

TeresaF wrote:

Does anyone have a tool which could be freely shared which can export grid references straight to shapefile based on polygon squares (like the export to shapefile in Recorder)?

I've written a small program to export a list of grid squares to a MIF/MID. Is that any use?

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

12

Re: OS Grid Refs and Eastings Northings

Hi Charlie, sounds interesting. Can you upload it somewhere? What is it written in?

-----------------
Teresa Frost | Wetland Bird Survey National Organiser | BTO
Other hat  | National Forum for Biological Recording Council
(Old hats  | NBN Board, ALERC Board, CBDC, KMBRC)

13

Re: OS Grid Refs and Eastings Northings

It's written in Python - I've temporarily uploaded it to https://docs.google.com/leaf?id=0B434gCrnNnwvNTdlZWU2YmItNTliYy00OGU0LWI0MmEtMGM5ZjA1ZGMwOWY2&hl=en_GB&authkey=CIe7298B

Haven't got the source code with me at the moment, but when I get round to uploading it to my website, I'll try to remember to mention it here (or send me a PM if you want it sooner).

Usual caveats apply, don't blame me if it doesn't work, emails your grandma, fries your computer &c. &c.

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

14 (edited by TeresaF 02-06-2011 09:14:57)

Re: OS Grid Refs and Eastings Northings

Steve's other thread prompted me to get around to uploading my spreadsheet:

Grid Reference Conversion to Eastings and Northings Utility.

Feel free to use the VBA code in it in other macros - don't sell it though. :D

As I said above, it gives Eastings and Northings of the mid-point and SW corner of the grid square along with the precision ("10km","2km" etc.). If you put a site centroid in as well it gives you the distance between the record and the site.

-----------------
Teresa Frost | Wetland Bird Survey National Organiser | BTO
Other hat  | National Forum for Biological Recording Council
(Old hats  | NBN Board, ALERC Board, CBDC, KMBRC)

15 (edited by BDeed 22-09-2014 11:31:57)

Re: OS Grid Refs and Eastings Northings

To resurrect an old thread. Does anyone know of an excel macro that will do this in reverse? Easting, Northings to Grid reference??

Never mind, found this: http://www.divshare.com/download/launch/1974666-473

LERC Officer
Merseyside BioBank - Local Environmental Records Centre for North Merseyside
www.MerseysideBioBank.org.uk

16

Re: OS Grid Refs and Eastings Northings

Have a look at http://gridreferencefinder.com/batchCon … onvert.php - this might help.

17

Re: OS Grid Refs and Eastings Northings

Cheers Matt, i had seen that and it seems to do what i was after but unfortunatley i was asked for something that worked specifically with spreadsheets!

LERC Officer
Merseyside BioBank - Local Environmental Records Centre for North Merseyside
www.MerseysideBioBank.org.uk

18

Re: OS Grid Refs and Eastings Northings

It does - just do a copy/ paste - I happily use it with data from Excel.  Drop the data  and associated other columns in - generate the new data - copy this and paste to a spreadsheet - data drops into separate columns no problem.