How To Extract Latitude From A Cell

Jul 9, 2014

I'm am trying to extract latitude from a cell that has the longitude and latitude separated by a space followed by a space and addition numbers. Here are three examples:

-89.8498682 35.467965 0
-89.849981 35.468031 82
-89.8500404 35.4681497 107

I tried: =MID(B262,LEN(B262)-LEN(C262),FIND(" ",RIGHT(B262,LEN(B262)-LEN(C262)))) where B2 is the source and C2 is the extracted longitude and it seems to work some of the time, but here is an example of when it doesn't:

-89.8499246 35.468019 0-89.8499246 6 35.46801

View 3 Replies

Extract Latitude And Longitude From Text

Feb 5, 2009

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.

View 5 Replies View Related

Latitude & Longitude Processing Help Please

Dec 12, 2009

I'm currently working on a project with data that includes columns for both latitude and longitude which I will need to convert from its current form into decimal degrees. The problem is that the coordinates are in the format DDDMMSS followed by a single letter indicating the direction, which means I have to first extract proper degree minute second format before I can do anything. Further complicating the problem is that each of the entries does not necessarily have all of the components I just described (eg some of the latitudes only have degrees and minutes but not seconds) and also if a longitude is less than 100 degrees, the third digit is not used in the data. There are over 2000 records so doing the conversion by hand just won't work and I've only got a few days to present this part of my project anyway.

I'm looking for a formula or series of formulas that will reliably convert something like 1083000W into -108.5000 when the last two zeros may or may not be there and the first digit may also be missing.

View 11 Replies View Related

Convert Zip Codes To Latitude And Longitude

Jul 15, 2014

I currently have an excel file of around 40.000 records with their zip/postal codes accompanying addresses in countries all over Europe, I would like to convert these into latitude and longitude coordinates.

View 2 Replies View Related

Latitude & Longitude -Text Separation

Mar 19, 2009

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.

View 14 Replies View Related

Compare List Of Latitude / Longitude

May 18, 2011

I have a list of 800 lat/long coordinates. I need to see how many of those 800 are within 3 miles of another list of 7,000 lat/long coordinates.

I have found the following formular to find the distance between 2 lat/longs:

=ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1))
*SIN(RADIANS(90-Lat2)) *COS(RADIANS(Long1-Long2))) *3958.756

Is there a formula that would allow me to run each of the 800 Lat/longs against the list of 7,000 and indicate how many on the original list have a match within 3 miles?

I was thinking a combination of countif with an array formula, but I can get the order of operations correct.

View 9 Replies View Related

Comparing Data Latitude And Lngitude

Sep 9, 2009

I have two lists of data that are next to each other an example is below:

A B C D E F g
1 Lattitude Longitude Speed Lattitude Longitude Speed
2 52.49 -2.97 30 52.49 -2.97 30
3 52.48 -2.96 30 52.48 -2.97 40
4 52.48 -2.97 30 52.48 -2.96 30

This table goes right down to cells 20099 so it is a huge set of data as you can see i have sorted both sets of data by latitude from highest to lowest.

My problem is that I need something that will go through the data and highlight locations that are identical with different speed limits. So if both the latitude and longitude are the same then compare the speed limit.

So in the above example cells A4 and B4 are the same as cells E3 and F3 and the speed limit is different so I would like this to be highlighted.

However if the Lattitude and longitude is the same and so is the speed limit it does not necessarily need to be highlighted

I can get this to work but only if the lat/longs that are the same is in cells next to each other. I do not know where to start if the identical locations are in separate parts of the spreadsheet.

View 9 Replies View Related

Converting Latitude/longitude To OSGB Grid Ref

Jul 31, 2008

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.

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.

View 9 Replies View Related

Splitting Large Numbers Into Groups (LATITUDE AND LONGITUDE)

Nov 16, 2009

I have a spreadsheet with a column of Longitude values like:
A
172828.383E

I want to somehow split the cell into new individual cells so it will be like this:

A B C
17 28 28.383

View 5 Replies View Related

Comparing Latitude And Longitude To Identify Closest Locations

Mar 23, 2013

I have list of address with zip code and Latitude and Longitude information. I am trying find a central locations, which will act as hub for rest of the address, by comparing distances from on locations to another.

Basically i need 2 things

1) How to identify the central locations
2) How to groups other locations around the central locations

Criteria here is distance

View 1 Replies View Related

Find Latitude And Longitude Within Radius And Within Given Slope And Return Number?

Feb 25, 2014

I have more than 1000000 coordinates with heights to sort through. The aim is to be able to give a specific radius and check all coordinates within this radius if the slope is more than a maximum slope. If this is the case it need to put the value (in this case) 100 in a new column. The reason for this is we have a reasonable flat terrain but the entire area is filled with Anthills. I need to sort the data. Normal ground points (No Anthills) should be labelled/coded as 200 and anthills as 100. This will allow my program to know the difference between the ground and anthills. In the tab "Input Sheet" I have a small portion of co-ordinates starting from row 8 to row 53 (this will have to extend all the way down to the last row in excel). I need to copy each row starting with row 8 (C8:E8) and paste it in row 2 (C2:E2). Column H indicates if the points are forming an anthill and the code needs to change. I have my final answer in the tab "Final Answer" that I require for my program. Is there any way I can write a VBA code that will check all the point instead of doing it manually.

View 4 Replies View Related

Find The Closest Match To Given Latitude / Longitude From Data Stored In 2 Columns?

Feb 2, 2014

I have 2 different sets of data: temperature data recorded at meteo-stations defined by latitude / longitude, and major world cities also defined by latitude / longitude.

After the user chooses the station's coordinates, temperature data is pulled out from the first data set; this works! However, I would like to be able to provide information about the closest city to the chosen meteo-station from the second data set.

File attached with sample data sets.

View 14 Replies View Related

Lookup Formula: Find The Longitude And Latitude Data From My "lookup" Sheet

Jan 28, 2009

In my workbook I have multiple sheets but I'm attaching a very simple workbook to demonstrate what I'm trying to accomplish. In my "Lookup" tab/sheet. I want to have known Latitude and Longitude data that will exist in columns A&B. Columns C & D will have address numbers and Street Name. I would like my lookup formula to find the longitude and latitude data from my "lookup" sheet, when the matching address information is typed in, in my 2009 sheet. I have to keep the street numerics and street name separate on this worksheet as well. I believe I'll need two separate lookup formulas as I need these formulas to start in cell G4 & H4 in my "GeoCoding1" sheet. Is it possible to have four columns of data to be viewed in a lookup formula? I tried this formula in cell G4 (GeoCoding1 sheet)

View 3 Replies View Related

Use Dynamic Formula Driven Text From Cell To Identify And Extract From Specific Cell?

Dec 21, 2013

I paste new data into a sheet of a monthly report I prepare. For this sheet, the # of data rows change (and is unpredictable) every month. I need the value inside a specific cell that dynamcially moves up and down based on the # of rows for that month (because it's below the rows of data).

So I made a formula to identify the exact cell # every month.

Example:

This month the exact cell is F255 in the "Refi" sheet.

So my formula in the "Summary" sheet cell A1 first finds the cell row # only (255) and since it's always column F, in B1 I have

VB:
="F" & (A1)

This outputs "F255" in B1, successfully identifying the target cell.

Now how do I write a formula in C1 to grab the value from whatever cell is named in B1. (For this month, the value in cell F255 from the "Refi" sheet)

View 7 Replies View Related

Extract Part Of Cell By Searching For Data Within Cell

Apr 18, 2013

I have some cells with text entered in column A.

These cells contain a time reference either in '09:30 - 16:30' format or just an 'AM/PM' format.

What I need to do is to populate column B with the part of the string that comes AFTER the times in the text (either in hh:mm format or AM/PM format)

Example
336 Data 2012 Classroom Induction 2012/08/28 09:30 - 16:30 data about this ------> data about this
336 Information about this 2013/04/04 09:00 - 13:00 Information 2 ------> Information 2
336 info CHS Suffolk 2013/05/07 PM DRC, BSE ------> DRC, BSE
364 information 2013/02/12 AM DRC, BSE ------> DRC, BSE

I cannot get this to work and cannot use Text To column as there is no character I can use to split it.

View 4 Replies View Related

Extract Data From Once Cell And List It For Each Cell?

Jul 10, 2014

I'm trying to extract data from once cell and list it for each cell where certain criteria are met. I'm using Index and Match...it works great for the first set of criteria, but returns the #REF for all following criteria. Here's my formula and I attached the file for your use:

"=INDEX($C$2:$C$160,MATCH(D$1,$B$2:$B$160,0),MATCH($A2,$F$2:$F$8,0))"

View 6 Replies View Related

Extract Text From Cell?

Mar 23, 2007

In Cell A1 Value is: 20070322_023047_002035_1112223333

above code is like Date(YYYYMMDD)_Time(HHMMSS)_Agent#_Phone#

I want result As per Following:

In Cell B1: 03/22/2007 Date(MM/DD/YYYY)
In Cell C1: 02:30:47 Time([HH]:MM:SS)
In cell D1: 002035
In cell E1: 111-222-3333

View 9 Replies View Related

Extract Date From Cell

Jul 8, 2014

In cell A1 i have a drop-down list.

In this list are the following:
London [S] (14/02/2013)
Rome [W] (23/10/2013)
Tokyo [W] (17/01/2014)
Berlin [A] (24/02/2014)
...and so on...

In cell B1 I would like to extract the currently selected city name and the [?] (e.g. "London [S]")
In cell C1 i would like to extract the currently selected date (e.g. (14/02/2013)

View 6 Replies View Related

Extract Number From Cell?

Aug 21, 2014

I have a reference in cell A2 I need excel to look at the text and extract the contents depending on the value example below

E600XXX4W1T12 - Excel to extract W1
E600XXX4D1T1 - Excel to extract D1
E600XXX4W10T12 - Excel to extract W10

So basicaly i need excel to extract the text from 4 to T

View 14 Replies View Related

Extract Numbers Only From Cell

May 6, 2009

I have a data set that I imported from Access. One of the columns contains the code for specific work activities, for example 13Z or 9A. I need to extract the numbers only from the cells in that column so that they are in separate cells in a separate column. I've been trying to use left, right, or mid functions, as well as text to columns with varying degrees of success.

View 2 Replies View Related

Extract String From Cell

Aug 20, 2009

In the following rows I want to extract the string that ends in K (ie everything after the final space). But I don't know the starting position or how long the string will be.

C2 9K
C1M (RB57) 4K
C2 4K
C6 FM (RB78) 120K
C2 9K
C1M (RB57) 3.5K
C2 (RB68) 12K

Can this be done?

View 8 Replies View Related

Extract Text From A Cell

Sep 19, 2009

I need a formulla which will extract the data from one cell into three different cells, I have three diffterent syntax added at the end of the text, for example I have below text in cell A2, I need to have Office installed on 69 PC s Below are the IP addresses 10.109.69.245 10.109.68.109 10.109.69.416 10.109.69.213 10.109.68.62 10.109.68.97 10.109.69.124 10.109.68.96 10.109.69.116 10.109.68.34 10.109.69.171 10.109.68.57 10.109.68.91 10.109.69.249 {MS Office Issue} ~Excel~ *Client Not Reachable*

{MS Office Issue} ~Excel~ *Client Not Reachable* are the three syntax that are there in the text, want need is, in cell B2 it should show "MS Office Issue", in cell C2 it should show "Excel" and in cell D2 it should show "Client Not Reachable" (without the quotes)

View 5 Replies View Related

Extract Certain Info From One Cell

May 7, 2013

Is it possible to extract certain info from one cell. Example in cell A I have a description of a product plus price I only need the price, The price has a $sign in front of it. Example below

I have over 2000 products so I was hoping I wouldn't half to go through one at a time. Is is possible to extract just price and place it into cell b.

1 "Damask 100% Silk Color: Creme Approx Width: 55"" Approx Rpt: V. 16, H. 13 Reproduction Circa: Late 16th Century Renaissance Revival Price $22.50 Stock Quantity: 107 YD

2 "Damask 100% Silk Color: Creme Approx Width: 55"" Approx Rpt: V. 16, H. 13 Reproduction Circa: Late 16th Century Renaissance Revival Price $28.50 Stock Quantity: 107 YD

3 "Damask 100% Silk Color: Creme Approx Width: 55"" Approx Rpt: V. 16, H. 13 Reproduction Circa: Late 16th Century Renaissance Revival Price $19.50 Stock Quantity: 107 YD

4 "Damask 100% Silk Color: Creme Approx Width: 55"" Approx Rpt: V. 16, H. 13 Reproduction Circa: Late 16th Century Renaissance Revival Price $119.50 Stock Quantity: 107 YD

5 "Damask 100% Silk Color: Creme Approx Width: 55"" Approx Rpt: V. 16, H. 13 Reproduction Circa: Late 16th Century Renaissance Revival Price $67 Stock Quantity: 107 YD

6 "Damask 100% Silk Color: Creme Approx Width: 55"" Approx Rpt: V. 16, H. 13 Reproduction Circa: Late 16th Century Renaissance Revival Price $89 Stock Quantity: 107 YD

7 "Damask 100% Silk Color: Creme Approx Width: 55"" Approx Rpt: V. 16, H. 13 Reproduction Circa: Late 16th Century Renaissance Revival Price $25.50 Stock Quantity: 107 YD

8 and so on

View 2 Replies View Related

Extract Time From A Cell

Dec 5, 2013

I have cells with the format

01/10/2013 12:02:00 AM

I need to extract only time and delete the date

View 3 Replies View Related

Extract Last 2 Words Of Cell?

Feb 26, 2014

How do I write a macro to extract the last two words of a cell?

View 10 Replies View Related

Extract The Defined Name From A Cell

Jun 10, 2009

So on my Excel spreadsheet I can select cell B2 of Sheet1 and using the name box give that cell a name such as "Important_Cell". I know using VBA I can access the cell using the name, such as:

View 4 Replies View Related

Extract Section Of Cell To Another?

Oct 6, 2011

I am wanting to select a section of a cells data, which can be shown in a selected cell using a formula i.e

AB1350309280000768436000076843

In the example above I want to exclude the first 8 digits and the last 1 from cell A1 and show it in B1

View 3 Replies View Related

Extract Month From Cell A1

Mar 8, 2012

ABC12/15/12february

extract the month from cell A1

View 4 Replies View Related

Extract Words From Cell

Nov 12, 2013

I have many cells that have full names in them. They are currently formatted as follows; last name, first name, middle name. I am trying to extract only the last name and first name (keeping the comma in between them) and drop the middle name.

View 9 Replies View Related

Extract Data From Cell?

Dec 11, 2013

if I had cells that contained values:X220 Lecture 1 [800]

X220 Lecture 2 [75.0] Is there a way to copy the value within each cell between the [ ] and paste that into another cell? As an example:

A
B

Lecture 1 [75.0]
75.0

Lecture 2 [100.0]
100.0

View 5 Replies View Related