+ Reply to Thread
Results 1 to 26 of 26

Latitude & Longitude-Text Separation

  1. #1
    Forum Contributor
    Join Date
    02-26-2009
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    286

    Smile Latitude & Longitude-Text Separation

    I am using Excel to calculate distances from latitude and longitude GPS positions. These positions have a format as follows:


    S36 39.113 E174 25.945


    These have been imported into a column, and using VBA I wish to separate
    latitude and longitude into two other columns. In both cases the minutes
    will eventually have to be express as decimals of the degrees. I don't
    mind if numerous columns are used as stages to get the final answer
    which is 36.234678 in one column and 174.15567 in the other.

    This can be done one bearing at a time or in a loop.

    Thanks Sandy
    Last edited by gsandy; 03-19-2009 at 08:47 PM.

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

    Re: Latitude & Longitude-Text Separation

    Is the format always degrees and decimal minutes?
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Contributor
    Join Date
    02-26-2009
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    286

    Re: Latitude & Longitude-Text Separation

    In most cases. Sometimes it is given as degrees, minutes & seconds (S 36 39 06 E 174 26 04) but I can convert this on my GPS into degrees and decimal minutes as I thought it would be easier to get into columns in this format.

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

    Re: Latitude & Longitude-Text Separation

    OK, assuming always degrees and minutes,
    Please Login or Register  to view this content.
    With a string in A1, select B1 and C1, and array-enter =LatLon(A1)
    Last edited by shg; 03-19-2009 at 03:55 PM.

  5. #5
    Forum Contributor
    Join Date
    02-26-2009
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    286

    Re: Latitude & Longitude-Text Separation

    Thanks but I am unsure what to do with the code and get it running. The VBA I am used to is in "Sub - End Sub" within a module. Always willing to learn new things. It is the "Function - End Function" that has me confused. Sandy

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

    Re: Latitude & Longitude-Text Separation

    Put it in a code module, just as you would a sub.

  7. #7
    Forum Contributor
    Join Date
    02-26-2009
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    286

    Re: Latitude & Longitude-Text Separation

    OK I have done that and put "=LatLon(A1)" into cells B1 & C1" etc, but get "#NAME" showing in the cells.

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

    Re: Latitude & Longitude-Text Separation

    Post the workbook as you have it.

  9. #9
    Forum Contributor
    Join Date
    02-26-2009
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    286

    Re: Latitude & Longitude-Text Separation

    Attached is file. I have left in the other routines that I have been playing with
    Attached Files Attached Files

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

    Re: Latitude & Longitude-Text Separation

    Code goes in a code module, not the sheet module, and you did not have the array formula entered correctly. You have to select BOTH cells, paste in the formula, hold down the Ctrl and Shift keys, then press Enter.
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    02-26-2009
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    286

    Re: Latitude & Longitude-Text Separation

    That works just fine - Thanks. I have now done an import from the GPS and when the coordinates are pasted into the spreadsheet they are automatically
    separate into two columns (previously I have been typing the bearing in rather that pasting) so the "S37 00.612 E174 47.549" bearing is being entered as "S37 00.612" in column B and "174 47.549" in column C. The "E" is dropping out. Could you tweak the code to accomodate this change? Hopefully once I see the code I will be able to modify it to work in other situations. Thanks Sandy

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

    Re: Latitude & Longitude-Text Separation

    If the E is dropped, how do you know the hemisphere?

  13. #13
    Forum Contributor
    Join Date
    02-26-2009
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    286

    Re: Latitude & Longitude-Text Separation

    I wont be flying out of the southern hemisphere in my microlight so it does not matter for the distance calculations. Why the E drops out in the pasting operation I dont know. If you look at the "Planning" sheet of my spreadsheet you will see the latitude and longitude in columns C and D. This is the format I am trying to end up with for the calculations. Thanks Sandy

  14. #14
    Registered User
    Join Date
    03-16-2009
    Location
    Your mom's basement
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Latitude & Longitude-Text Separation

    Sounds like you just completed a homework problem for her....

  15. #15
    Forum Contributor
    Join Date
    02-26-2009
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    286

    Re: Latitude & Longitude-Text Separation

    Its a him. Once under way others will be using it for flight planning. And after that I have similar work situations where I will be altering the code to suit to solve other problems. Sandy

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

    Re: Latitude & Longitude-Text Separation

    Having lat/lon in separate cells makes in easier and more flexible:
    Please Login or Register  to view this content.
    Examples:
    Please Login or Register  to view this content.
    The regulr old (non-array) formula in B2 and copied down is =LatLon(A2)

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

    Re: Latitude & Longitude-Text Separation

    I have some other functions that compute distance between lat/lons and departure/return headings if you're interested.

  18. #18
    Forum Contributor
    Join Date
    02-26-2009
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    286

    Re: Latitude & Longitude-Text Separation

    Yes please. Are you into flying? Sandy

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

    Re: Latitude & Longitude-Text Separation

    Are you into flying?
    Used to be.

    This computes the central angle in radians between two lat/lons. Multiply the result by 3440 to get nautical miles.
    Please Login or Register  to view this content.
    DepHdg computes the departure heading from the first lat/lon to the second. The other two functions are called by DepHdg.
    Please Login or Register  to view this content.

  20. #20
    Forum Contributor
    Join Date
    02-26-2009
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    286

    Re: Latitude & Longitude-Text Separation

    Thanks now have "LatLon" working well.
    I have put your new code into a module but I am unsure how to use it. Can I use it with the destinations shown on my "Planning" sheet where the destinations are on different rows?
    Thanks for your help so far Sandy

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

    Re: Latitude & Longitude-Text Separation

    Can I use it with the destinations shown on my "Planning" sheet where the destinations are on different rows?
    Sure: = 3440 * CentralAngle(A1, B1, A2, B2)

  22. #22
    Forum Contributor
    Join Date
    02-26-2009
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    286

    Re: Latitude & Longitude-Text Separation

    Go that working, arrays are something else in Excel I will have to do more study on. My microlight aircraft is MW6S built from plans by Mike Whitaker, first flight this year, some of my construction photos can be viewed in MWClub.org.
    Thanks again Sandy

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

    Re: Latitude & Longitude-Text Separation

    That's very cool.

    I've flown an ultralight once, from the front seat of a two-place with tandem seating. Never had vertigo while flying before, but sitting on a lawn chair surrounded by nothing but air was ... unique.

  24. #24
    Registered User
    Join Date
    10-18-2012
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Latitude & Longitude-Text Separation

    Hi All,
    I have tried to run the code but it seems like am still not getting it. Somebody please show me how to do it step by step.

    Eddie

  25. #25
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Latitude & Longitude-Text Separation

    kipedie,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  26. #26
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Latitude & Longitude-Text Separation

    The E doesn't differentiate the southern hemisphere - its for East. With longitudes like E174 25.945, you're less than 500km from changing hemisperes from east to west. If you don't replace 'W' with '-' you'll get the wrong results.
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

+ 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