Plotting points using coordinates from excel [duplicate]

Question

I have an excel spreadsheet with co-ordinates (eg. S31 07 35.3). I would like to use these co-ordinates in arcmap (version 10.2) to plot points based on these coordinates. How do I go about doing this because I cannot use the co-ordinates as is? So basically, I need a way of converting these co-ordinates into something arcmap will understand.

Answer 1

You have coordinates in DMS (degree minute second) format, and need to get them into DD (decimal degree) to import easily into ArcMap.

While in Excel, make a new column. This would be the formula to just convert from DMS to DD: degrees, plus minutes divided by 60, plus seconds divided by 3600.

=MID([DMS], 2, 2)+(MID([DMS], 5, 2)/60)+(MID(A6, 8, 4)/3600)

Note that [DMS] should be replaced with whatever cell you're calculating from, e.g. A1 or D2.

Since you've got the hemisphere at the beginning, you need to include a condition that if it's S or W, the DD result should be negative. (Since both S and W are "larger" than N and E, one conditional can work for both.)

=IF(LEFT([DMS], 1)>"N", -1, 1)*((MID([DMS], 2, 2)+(MID([DMS], 5, 2)/60)+(MID([DMS], 8, 4)/3600)))

This should produce a column of values that is in the appropriate format for ArcMap, assuming your input coordinates are consistently formatted with a letter prefix and spaces between the D/M/S.

After that the points can be imported as XY events.

---

Alternatively, it's possible to do that conversion process with a script or even after the data is inside ArcMap. This knowledge base technical article describes the process with Field Calculator on a table: Convert a file with coordinates in degrees, minutes and seconds to a shapefile using ArcMap. I personally prefer doing the calculation beforehand in Excel because it's easier to see if something's gone wrong (e.g., some of the coordinates are differently formatted)

Answer 2

The following ESRI Knowledge Base artcile details the steps to turn your data from Excel into a shapefile.

Hopefully you will find this is all you need.

http://support.esri.com/en/knowledgebase/techarticles/detail/27589

Note: You will have to convert your coordinates to decimal degrees.

Following @Erica's comment, and indeed a re-reading of your question, you may find the following link of use.

http://www.mrexcel.com/forum/excel-questions/669123-how-convert-degree-min-sec-decimal-degree-lat-longitude.html