+ Reply to Thread
Results 1 to 7 of 7

Convert Latitude longitude to 6 digit OS grid Ref

  1. #1
    Registered User
    Join Date
    09-20-2011
    Location
    N.Wales
    MS-Off Ver
    Excel 2010
    Posts
    63

    Convert Latitude longitude to 6 digit OS grid Ref

    Hi,

    I am looking for a macro or formula to convert GB Latitude longitude Ref to 6 digit OS grid Ref. I have 20 + Latitude longitude Ref to convert at a time,

    Thank you for looking at this,

    Cheers

    WCE

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,476

    Re: Convert Latitude longitude to 6 digit OS grid Ref

    I have not seen one. Not being in Britain, I probably would never have known anything about OS grid, so thank you for helping me learn something new today.

    In the spirit of "let me google that for you," I put this into a search engine, and this http://www.movable-type.co.uk/script...g-gridref.html was one of the first sites the search returned. He explains that "the maths [for converting lat/long to OS grid] are very complex." He also includes a functional calculator and javascript source code for his conversion. Someone who is familiar with coding could probably adapt the javascript code to a spreadsheet and/or VBA procedures to perform the same calculation.

    He also has a link to the Ordnance Survey's website Guide to coordinate systems in Great Britain, where they appear to have a calculator and a 2002 Excel spreadsheet for these calculations.

    Hopefully that will give you a good start.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Convert Latitude longitude to 6 digit OS grid Ref

    Hi WCE_123

    Try the Ordinance Survey site.

    http://www.ordnancesurvey.co.uk/oswebsite/index.html

    Excel Sreadsheet

    Coordinate transformer
    Regards Kevin


    Merged Cells (They are the work of the devil!!!)

  4. #4
    Registered User
    Join Date
    09-20-2011
    Location
    N.Wales
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: Convert Latitude longitude to 6 digit OS grid Ref

    Thank you for your reply - The problem I have is the bulk of conversions I have to do as mentioned this could be as many as 60, I have this task most days so you can understand the need for a conversion tool to deal with multiple inputs instead of one at a time,

    Cheers again

    WCE

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Convert Latitude longitude to 6 digit OS grid Ref

    See attached.
    Attached Files Attached Files
    Last edited by shg; 05-24-2013 at 02:13 PM.
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: Convert Latitude longitude to 6 digit OS grid Ref

    All the code you need is in the worksheet that can be downloaded from the OS Site as MrShorty mentioned.

    You could even automate entering co-ordinates in the 'Enter coordinates here' worksheet and reading back the OS grid reference.

    That could be done for as many sets of lat/long you wanted.
    If posting code please use code tags, see here.

  7. #7
    Registered User
    Join Date
    09-20-2011
    Location
    N.Wales
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: Convert Latitude longitude to 6 digit OS grid Ref

    Hi Norie,

    Would it be possible to help with the automating of the co-ordinate input and reading back, this would be great,

    Cheers

    WCE

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1