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

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

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

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

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?

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

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.

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