Converting Latitude/longitude to OSGB grid ref

Hi,

I'm very new to using Excel for programming etc, so there may well be an easy fix for my problem.

I'm using excel to try and sort by distance a list of wind farm locations from a location i put in.

This should be relitavely straight forward, however there are a few elements that make it more complicated.

I am getting a list of wind farm locations from the internet, the BWEA site. The latitude and longitude data copied over is in text. I'm using Excel '07 but it still requires me to butcher the numbers out of it with several columns of REPLACE formulas, it isn't elegant and may well be wrong, so a better way of doing this would help a lot.

The second and harder problem is that I have to first convert the latitude and lonitude values into OSGB grid references. So far i have found the OS maps excel converter, but it only converts one value at a time. I need something that will convert all of them at once (i don't want to go through and individually type in each distance).

After that it is possible to use basic trigonometry to find the distance between them and any grid reference i put in.

Cheers for any help you can give

Tom

I have spent this afternoon producing a reduced version of the spreadsheet that hopefully doesn't need a macro - However it means the formulaes involved are exceptionally long and complicated.

What is more annoying is that it still doesn't come put with the right answer!! -And I don't know why, only that there is an error before the column named 'v' (column z). It may be a simple error (equivalent to looking for my glasses when they're on my head) But i can't seem to spot it.

The helpful guide i am using is from the OS website - the Pdf. Appendix C is what i am trying to do!

Again any help is much appreciated.

Cheers

T

I'll look at this later today if no one else solves it in the meanwhile.

The goal is just to convert WGS-84 lat/long coordinates (received as d m s in a space-delimited string) to OSGB coordinates?

Hi,

Yup basically that's it! - however, it's a lot harder than it first appears (or so i've found!)

Thanks a lot for havinga look at it.

T

It's the first section in App C, correct?

Does the worked example use the same phi0 and lambda0 (49 and -2 respectively) that you show in the spreadsheet?

For the worked example, the function returns the Easting exactly in accordance with the worked example, and the Northing with a 0.001% error. I don't see a mistake in the formulas, but did not look carefully.

The UDF requires a two-cell wide array formula. Usage is

=WGS84ToOSGB(lat, lon) (arguments in radians, north and east positive)

Array formulas MUST be confirmed with Ctrl+Shift+Enter, not Enter. You'll know you did it correctly if curly braces appear around the formula in the Formula Bar; you cannot type in the braces directly.

Wow!

Thanks that looks great - im just going to try it out now!

Thanks so much for the help. I hope it wasn't too much hassle!

T

Right I think i got it almost fully sorted!

There is a little niggle in that the triginometry bit to figure out the distances between the input location (in green) and the wind farms aren't quite right, but i hope i shall be able to find out what it is and sort it by later on today.

I've attached the file so you can see what you have helped create (and also in the hope you (or someone else) could see what is causing the distances to go wrong in the 'Reformatted ....' sheets).

Thanks again for your help

T

Fantastic it's all done and dusted

Thank you very much! You have been a really great help!



Greenspan

Hi, I've only just joined the forum and already it's solved a problem that's been bugging me for a while: how to convert a batch of GPS locations in lat/long form to OS grid references. This routine is great. My lat/long values are actually in decimal degrees, not dms, but by substituting the function RADIANS(angle), where angle is the lat or long in my format, for the version in the download, I've now got just what I want. Thanks.

Nice, I didnt think this was possible

This is what I am looking for also. I am a newbie in excel formulas and all looks really difficult to me. I will try to use the formula shg provided and see if it works on mine lat/long lists I have got. Many thanks in advance.

Hi Guys
Very new to excel, but here goes.
Is there a way of batch converting Lat/Long to OS grids with alpha numerical,10 digit (55.920860,-4.402973 is NS49857 72357)