+ Reply to Thread
Results 1 to 6 of 6

Extract latitude and longitude from text

  1. #1
    Registered User
    Join Date
    02-05-2009
    Location
    New London, CT
    MS-Off Ver
    Excel 2007
    Posts
    3

    Extract latitude and longitude from text

    Good Afternoon, I am currently working on a project and need some help data mining a extract from a database that's in excel.

    Some of the data I was given has latitudes and longitudes which are valuable to me but some of the information was entered incorrectly and the lat and long columns have only zeroes for some entries. I used some VBA to sort out all the ones that have zeroes in the lat and long columns because I noticed in a comments block some of them have the latitude and longitude placed in the comments instead of the designated spot.

    An example of one of the comment cells is this:
    Search and Rescue 30NM S MARTHA'S VINEYARD 40-37.4N 070-43.3W MEDEVAC OF 35YOM E

    As such the problem I am having is figuring out how to search through the cells for ones that have a lat and long in them since there is other information and numbers in the comment cell. Keep the cells that have latitudes and longitudes and get rid of the rest. The red highlighted information is what is valuable to me. Another problem is not all of the latitudes are in that format sadly as can be seen by this example cell:

    SAR Unreported Vessel/ 42 01. N 067 25. W CLOSED AREA II/041527ZAUG06

    If anyone has any insight/references into how I could go about doing this I would pretty much owe you my life since doing it manually would take ages for myself and my group on this project.

    Thanks!
    Last edited by Almighty; 02-05-2009 at 06:26 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: Extract latitude and longitude from text

    Welcome to the forum.

    How about posting a workbook that contains an example of every type of lat/lon data you have, and examples of what should be left after processing.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    02-05-2009
    Location
    New London, CT
    MS-Off Ver
    Excel 2007
    Posts
    3

    re: Extract latitude and longitude from text

    Thank you its good to be on the forums! I appreciate the quick response.

    I have uploaded an excel 2007 macro enabled workbook. This is a cross section of data with columns of information removed to illustrate the task I am trying to undertake since the data is sensitive and unfortunately cannot be openly shared.
    This file has the information for lat/long incorrectly input like I said in columns. I have already removed the rows that have correctly inputted lat and longs and placed them in another set of excel files for use in my study.

    In the Case Title (Column D) for each entry is the comments block I need to examine to see if any lat/longs were recorded in there.

    Rows 4 - 9 are examples of rows I can just get rid of as they have no amplifying information of what I need.

    Rows 5, 82, 105, 115, 127 are examples of rows of data that need to be kept as they have a discernible lat and long recorded in them.

    As I noted previously there is A LOT more data(50,000+ rows of data and multiple workbook files) then this so manual checking,while an option is VERY time consuming. Again, not all lat long entries follow the same format as I noted above in my first post. I understand I may have to run several filtering loops of code to pick out all the different formats, but I really have no basic idea how to check within a cell for things that contain certain characters/formats/etc.

    Again I really appreciate the help.
    Attached Files Attached Files
    Last edited by Almighty; 02-05-2009 at 02:09 PM.

  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: Extract latitude and longitude from text

    Put the code below in a code module, then select whatever bunch of cells you want in column D and run the sub. It puts lat one cell to the right, and lon two cells to the right, both in decimal degrees.
    Please Login or Register  to view this content.
    You will need to set a reference to Microsoft VBScript Regular Expressions
    Last edited by shg; 02-05-2009 at 03:30 PM.

  5. #5
    Registered User
    Join Date
    02-05-2009
    Location
    New London, CT
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Extract latitude and longitude from text

    THAT is amazing and worked like a charm. Thank you very much for your help. I didn't know anything about VBScripts RegExp until I looked it up. Could you explain more on:

    Please Login or Register  to view this content.
    as I assume that has to do with the formatting issue I was facing? But pretty much everything else I understand.

    Also I would love to document your help in the report I am writing. If you would like to be recognized PM me and it can be arranged. MAN its good to learn something new! Thanks a million for the help.
    Last edited by shg; 02-05-2009 at 06:07 PM. Reason: deleted spurious quote

  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: Extract latitude and longitude from text

    Could you explain more on:

    "\D*(\d{1,3}[ -]\d{1,2}\.\d{1,3} [NS]).*"
    Look for 0 or more non-digit characters followed by (1 to 3 digits, a space or a hyphen, 1 or 2 digits, a decimal point, 1 to 3 digits, and the letters N or S). Return the part within the parens.
    If you would like to be recognized ...
    No recognition required, thanks.

    Would you please mark the thread as Solved?

    Click the Edit button on your first post in the thread

    Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes

+ 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