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.
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)
[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)
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.
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.