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
ADVERTISEMENT
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
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
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
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
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
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
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
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
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
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
Aug 31, 2010
I have imported a tab delimited text file into excel. One of the columns consists of a string of text - a comment section. Within this column, there are periods and for some reason it seems that Excel is treating these periods as row separators. how I can prevent Excel from separating the text into rows?
View 3 Replies
View Related
Feb 14, 2009
I don't know if the title explains it, but I was given an Excel Directory type file (name, address, telephone etc.) which has been giving me issues with various exports because of how it's formatted.
Specifically there is one names column with "Last Name, First Name" and then a "City, State Zip" column. I'm hoping that there is a way I can separate the "Name" column by extracting just the last names and copying them to a new column. Likewise I would like to be able to extract City, State and Zip and put them each in their own columns. Is this even possible to do?
View 4 Replies
View Related
Jun 20, 2014
I have a grand net income and net loss rows and underneath those are costs which are like the breakouts of the income and loss. I need a function that spits out a separate total income (meaning sum of all the positive numbers of those breakouts) and separate total loss( meaning the sum of all the negative numbers within those breakouts).
In addition, I have a separate table (on my spreadsheet it starts on cell B13 and that table has the total for each of the breakouts. I want to have a function on that table too that when the number is negative, the number will appear on the Net Loss column. If it's positive, then it appears on the grand income column.
View 1 Replies
View Related
Aug 19, 2013
I receive a monthly spreadsheet from our trucking company which tracks the package and weight of that package. However I need to pull the weight out and run a report on total weight. However it is a mix of pounds and kilograms. Please see my example. What I want is a final column of all weight converted to kilograms. Note as well the spacing in the data as sometimes the "lbs" is right next to the number and other times there is a space between them.
Raw Data
package 1
10.2kgs
package 2
5 kgs
[Code] ........
View 3 Replies
View Related
May 14, 2014
I want to write a formula/script that searches file names in Column "A" for an item number located in Column "B", then combines all file names containing that item number into Column "C" with comma separations. Each item number will have a different number of file names associated with it (between 1 and 10 files), and there are about 2000 different item numbers being crossed with about 7000 file names.
Here is an example completed with a CONCATENATE formula:
File Name
59481A_1.jpg
59481A_2.jpg
59481A_3.jpg
59481A_4.jpg
59481A_5.jpg
59481A_6.jpg
Q110XL_1.jpg
Q110XL_2.jpg
C5710_1.jpg
C5710_2.jpg
C5710_3.jpg
C5710_4.jpg
Item Number
59481A
Q110XL
C5710
Final Image List
59481A_1.jpg,59481A_2.jpg,59481A_3.jpg,59481A_4.jpg
Q110XL_1.jpg,Q110XL_2.jpg
C5710_1.jpg,C5710_2.jpg,C5710_3.jpg,C5710_4.jpg
View 4 Replies
View Related
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
View Related
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
Aug 10, 2007
Having the Zip Code Tabulation Module from the US Census Bureau, I have the following and much more. But based on the 3 items :
5-digit Zip Code, Longitude, Latitude.
I'm wanting to Select a Zip Code and have (let's say) several Zip Codes plotted (maybe 4 - 10) out from the center point of the map.
either with, or without a connecting straight line .
And possibly a Scalable Miliage indicator |---- 10 Miles ----| which would be based on the distance.
View 9 Replies
View Related
Oct 14, 2008
1. Search an excel sheet "column" for a particular type of text and insert values based on that text (if found) in another column.
e.g I have column A1 to A10 with different types of text. I would like to search for the keyword "Risk is high" OR "Risk=High" for each cell in the column and insert a "1" if found beside it's corresponding "B" column. If not found, I would like to insert a "0".
So, if the text "Risk is high" OR "Risk=High" was only found in A6, I would like B6 to be "1". Rest of the values in the B column would be "0's", since the text was not found in any of the other cells.
View 10 Replies
View Related
Nov 21, 2007
I have a macro which imports data from a mainframe dump text file and performs 'Text to Columns' on the imported data so that formula in the spreadsheet can act on the data. The code works perfectly well when I use it, but if a different user logs on and performs exactly the same mainframe dump and import macro the Text to Columns action splits the raw data in a different way and the result is that the split renders the formulae useless.
I've experimented a little and for some reason it appears that the 'Field Info' parameters which are produced when the Text to Columns function is recorded in a macro differ between users even though the raw data is exactly the same.
FieldInfo:= _
Array(Array(0, 1), Array(18, 1), Array(35, 1), Array(56, 1), Array(70, 1), Array(88, 1), _
Array(102, 1))
View 6 Replies
View Related
Apr 10, 2013
I'm trying to get something like - If a cell contains certain text AND certain text then display set text OR If a cell contain certain text AND certain text then display set text.
For example:
Vauxhall Corsa 1.5Litre Petrol = vauxhall corsa petrol
Vauxhall Corsa 1.5Litre Deisel = vauxhall corsa Deisel
Vauxhall Nova 1 litre Petrol = vauxhall nova petrol
So it the formula is effectively searching for the car type and the fuel type and then returning the relevant set text. So far I'ver only been able to find IF functions criteria to identify one feature of the text.
I'm thinking it's got to be variation on =IF(ISNUMBER(FIND("Vauxhall Corsa",A1)),"Vauxhall Corsa Petrol") but there should be something else in there like =IF(ISNUMBER(FIND("Vauxhall Corsa"&"Petrol",A1)),"Vauxhall Corsa Petrol")
I know this is wrong but just trying illustrate my thinking. I need it to pick up vauxhall corsa AND petrol. I understand that I would have to stick a lot of IF functions together in a string, but that is fine as long as I can ask it to pick certain text AND certain text within a cell.
View 6 Replies
View Related
Apr 30, 2014
I have 10 cells in a column. I have a drop down list in each that is the same in each. If all 10 cells have the same item selected from the drop down list, I want a separate cell to list Yes or No. I've tried a few variations with no luck.
View 4 Replies
View Related
Sep 18, 2009
I have an existing spreadsheet with a column of strings (actually VIN numbers). These numbers correllate to a bunch of text files, that can exist in one of three folders (UsernameDesktop1, 2, or 3) on my desktop. What I need the macro to do is:
1) get the filename from A2 (A1 is a heading row)
2) Find the appropriate text file in one of the three folders
3) Put the folder name into I2
4) Scan the text file for some strings, and copy some data that follows those strings into J2:O2 (I can handle programming this)
5) Close the text file
6) repeat above for the remainder of filenames (about 1800 files)
View 3 Replies
View Related
Aug 16, 2013
So I have been playing around with the tab colors on my workbook and am trying to figure out the correlation between color brightness and the auto change from black text to white text. I've noticed that if the Green color value is higher, excel is more likely to use the black text. If Blue is high, white. Green takes precedence over Blue and Red is just kind of in its own world.how excel calculates this?
View 2 Replies
View Related
May 14, 2012
I am trying to find a formula that will count the number of unique entries there. I have tried the solutions posted on various websites to no avail (most recently:
Code:
=SUM(IF(FREQUENCY(MATCH(A1:A10,A1:A10,0),MATCH(A1:A10,A1:A10,0))>0,1))
).
The answer should be 4,457.
Ticket Number
T20110819.0527
T20110830.0339
T20110901.0060
T20110901.0060
T20110907.0042
T20110907.0042
T20110908.0186
T20110908.0186
T20110908.0186
T20110908.0186
[code].....
View 1 Replies
View Related
May 16, 2013
Is there a macro that will allow me to create text to row from a comma separated cell, but also associate the cell to its left automatically?
The table below exemplifies what I need. The top of the spreadsheet is how my data is currently. The portion after the break is how I would like it to be.
I have over 600 rows that needs this done.
1,2,3,4
B
[Code]....
View 3 Replies
View Related
Mar 18, 2014
I am my excel worksheet (excel 2010) I have one cell that changes every day (number). I want this number to open my htm document and replace the same number in a string in the htm and save/close this.
An example:
My htm document is located at C:/ and named XX.htm
The number I want from excel is in cell A1 in sheet1, and the worksheet is located in D:/ named yy.xlsx
And the text(number) I want to replace is in the following string in the htm document, in this string it is 72, next day it can be 30:
src="Bilder/72.png"
View 8 Replies
View Related
Jul 19, 2014
I have some text in a cell, which is longer than the width that I am going to make that cell. When I don't wrap the text, it cuts it off at the end of the cell.
Since each column represents a day in a calendar, it would be incorrect to merge the two cells, but I don't want the text hidden, since I need to print the final product.
It is non-numeric text, and i've pasted --> values to remove formula issues.
View 1 Replies
View Related
Oct 10, 2007
When I copy from Excel 2003 (values & formulas) and paste special into Excel 2007 i get the option screen to select unicode text,sylk etc instead of the other screen with the option of values,formulas,formats etc.How can I select the option for value,formulas?
Sorry cannot attach a screen shot as it is above the allowed limit.
View 9 Replies
View Related
