How to Clean Messy Coordinate Data

Coordinate data rarely stays clean as it moves between people, tools, and documents. Each handoff introduces opportunities for formatting changes, truncation, and corruption. Understanding the common causes helps you recognise the pattern faster when you encounter it.

Spreadsheets are one of the biggest culprits. Excel and Google Sheets may automatically reformat numbers, strip leading zeros, convert text to dates, or truncate decimal places without warning. A DMS coordinate like 51°29'19.91" can be mangled beyond recognition by a single auto-format step.

Copy-pasting from PDFs and emails introduces character substitution problems. Degree symbols (°) get replaced with superscript o or the letter o. Prime marks (' and ") get replaced with curly quotes or backticks. Minus signs get replaced with en-dashes or em-dashes. These substitutions make the coordinate look nearly correct to a human reader while being completely unparseable by software.

Manual data entry adds its own issues: transposed digits, missing hemisphere indicators, inconsistent separators, and mixed formats within the same dataset.

Before cleaning anything, look at the data and determine which coordinate format or formats are present. This is the single most important diagnostic step, because the cleaning strategy depends entirely on the format.

Ask yourself these questions: Do the values look like small decimal numbers (e.g. 51.5074, -0.1278)? That is probably Decimal Degrees. Do they include degree symbols and minute/second notation? That is DMS or DDM. Are they large numbers in the hundreds of thousands (e.g. 530034, 179382)? That is likely Easting/Northing. Do they start with two letters followed by digits (e.g. TQ 379 785)? That is a UK Grid reference. Do they include a zone number and two large values (e.g. 30N 699745 5710157)? That is UTM.

If the values are a bare pair of large numbers and the clues run out there, paste one row into the Coordinate Format Identifier. It ranks the systems and zones the pair could belong to, which usually settles what the whole column is before you start cleaning it.

One of the most treacherous problems in coordinate data is a dataset that contains multiple formats mixed together. This happens more often than you might expect, especially when data has been compiled from different sources, merged from multiple spreadsheets, or collected over a long period by different people.

A column that contains mostly Decimal Degrees might have a few DMS values scattered through it. A UK Grid reference list might include some raw Easting/Northing pairs where the grid square letters were lost. A UTM dataset might have a few rows where the zone number is missing.

If you try to batch-process a mixed-format dataset as if it were all one format, the mismatched rows will silently produce wrong results. Always scan the data visually before processing. Look for rows that are significantly longer or shorter than the others, values that include different characters, or numbers that fall outside the expected range for the assumed format.

Once you know the format and have confirmed the dataset is consistent, you can start fixing the most common formatting issues:

DMS and DDM coordinates are the most vulnerable to formatting corruption because they use special characters (°, ', ") that do not survive copy-paste operations cleanly. Here is a targeted cleaning checklist for DMS/DDM data:

First, check that every coordinate has a degree symbol, a minutes mark, and (for DMS) a seconds mark. If any are missing, the parser may not be able to separate the components correctly. Second, check that the hemisphere letter (N, S, E, W) is present and in the right position. Some sources put it at the start (N51°29'19.91"), others at the end (51°29'19.91"N). Both can work, but the dataset should be consistent.

Third, check for spaces. Some DMS values have spaces between the degree, minute, and second components; others do not. Both can work, but mixing the two styles in the same dataset can cause batch processing issues. Finally, check that the seconds value (in DMS) is not being confused with the decimal minutes value (in DDM). A value of 51°29.332' is DDM, not DMS. The absence of a seconds component is the distinguishing feature.

UK Grid references and Easting/Northing values have their own set of common issues:

Grid square letters: if the two-letter prefix has been separated from the digits by a line break, tab, or column boundary, reassemble them. A grid reference split across two spreadsheet columns (one for the letters, one for the digits) needs to be concatenated before conversion.

Unbalanced digits: the numeric part of a grid reference must have an even number of digits, split equally between Easting and Northing. If the total is odd, a digit has been lost. Check the original source if possible.

Easting/Northing order: Easting should come first. If your data source has Northing first (which some legacy systems use), the values need to be swapped before conversion.

Decimal points in grid references: UK Grid references are whole numbers. If you see decimal points in what looks like a grid reference, it may actually be a latitude/longitude pair or a different coordinate system entirely.

UTM coordinates need three pieces of information to be interpreted correctly: the zone number, the hemisphere letter (N or S), and the Easting and Northing values. If any component is missing, the coordinate is ambiguous.

The most common issue with UTM data is the missing zone. When UTM coordinates are stored in a spreadsheet, the zone is sometimes kept in a separate column or omitted entirely. Without the zone, the Easting and Northing values could refer to any of 60 different locations around the world.

Another common issue is confusion between the hemisphere letter and the latitude band letter. UTM zone 30N means zone 30, northern hemisphere. UTM zone 30U means zone 30, latitude band U (which is also in the northern hemisphere, but the notation is different). Most converters accept both, but it is worth being aware of the distinction when cleaning data from technical sources.

After cleaning, the most important step is to test a sample of the data in a converter and verify the results on a map. Do not assume the cleaning was successful just because the values look correct to your eye.

Paste a representative sample of cleaned coordinates into CoordinateMapper and check that the parser identifies the format correctly, that the plotted locations appear in the expected geographic area, and that the converted outputs match what you would expect for those locations.

If some points appear in unexpected places, go back to those specific rows and check for remaining formatting issues. A systematic sample test, for example the first, middle, and last rows of the dataset, can catch most problems without testing every single row.

This test matters most when the cleaned data is headed for GIS software - QGIS and ArcGIS import dialogs fail silently on rows that do not parse, so catching problems here saves a confusing debugging session later. See How to Import Coordinates into QGIS and ArcGIS for that side of the workflow.

While cleaning is sometimes unavoidable, a few practices reduce the frequency and severity of messy coordinate data:

Cleaning messy coordinate data is a four-step process: identify the format, check for mixed formats, fix the common formatting problems for that specific format type, and test the cleaned result in a converter. Most coordinate mess comes from copy-paste corruption, spreadsheet auto-formatting, and mixed formats within a single dataset. A systematic cleaning approach saves far more time than trying to fix each row individually, and testing the result on a map before using it downstream prevents the most consequential mistakes from reaching the next stage of your workflow.