Excel Formulas for Cleaning Geographic Coordinates with Degree Symbols

Wrangling geographic coordinates clogged with degree symbols (°) in Excel is a notorious bottleneck for data analysts. While securing organizational funding for dedicated GIS software or spatial databases typically addresses this upstream, analysts must frequently cleanse raw Excel exports manually. Standardizing this data grants immediate analytical readiness and prevents downstream mapping errors. However, one key stipulation is that your formulas must accommodate varied inputs, such as converting "40.7484° N" or "-73.9857°" into clean, decimal numbers. Below, we outline the exact nested Excel formulas required to automate this coordinate cleanup process efficiently.

Introduction

Geographic coordinate data is the backbone of modern mapping, logistics, and spatial analysis. However, when exporting coordinate data from GPS devices, online mapping tools, or legacy databases, it often arrives in Excel as poorly formatted text. The most common culprit? The degree symbol (°), accompanied by minutes ('), seconds ("), directional indicators (N, S, E, W), and unpredictable spacing.

To use these coordinates in mapping software like ArcGIS, Google Earth, or Tableau, or to run distance calculations, you must convert them into clean, standardized Decimal Degrees (DD). This comprehensive guide walks you through the exact Excel formulas required to clean geographic coordinates, strip out degree symbols, and convert complex Degrees, Minutes, Seconds (DMS) values into clean decimal formats.

Understanding the Data Formats

Before diving into the formulas, it is crucial to recognize the format of your raw data. Typically, raw coordinates fall into one of three categories:

Scenario 1: Removing Degree Symbols and Suffixes from Decimal Degrees

If your coordinates are already in decimal form but contain the degree symbol (°) and directional letters (N, S, E, W), your goal is to strip the non-numeric characters and apply a negative sign for South (S) and West (W) coordinates.

The Excel Formula

Assuming your raw coordinate is in cell A2, use the following formula to clean it:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "°", ""), "N", ""), "E", ""), "S", "-"), "W", "-") * 1

How this Formula Works:

• Nested SUBSTITUTE functions: The formula systematically replaces target characters. First, it removes the degree symbol (°). Next, it eliminates "N" and "E" because North and East are represented by positive numbers.
• Handling South and West: It replaces "S" and "W" with a minus sign (-).
• The * 1 Trick: Excel treats the output of string manipulation as text. Multiplying the final output by 1 forces Excel to convert the cleaned text string back into a functional, calculations-ready numeric value. If a negative sign was placed at the end (e.g., "73.9857-"), Excel's calculation engine automatically parses it into a standard negative number (e.g., -73.9857).

Scenario 2: Converting Degrees, Minutes, Seconds (DMS) to Decimal Degrees

Converting DMS (e.g., 40° 44' 54.3" N) to Decimal Degrees requires converting minutes and seconds to fractions of a degree. The mathematical formula is:

Decimal Degrees = Degrees + (Minutes / 60) + (Seconds / 3600)

To do this dynamically in Excel, we must isolate the numeric portions between the symbols (°, ', and ").

The Master DMS Conversion Formula

With your raw DMS coordinate in cell A2, copy and paste this robust formula:

=IF(A2="", "", (LEFT(A2, FIND("°", A2) - 1) 
+ MID(A2, FIND("°", A2) + 1, FIND("'", A2) - FIND("°", A2) - 1) / 60 
+ MID(A2, FIND("'", A2) + 1, FIND(CHAR(34), A2) - FIND("'", A2) - 1) / 3600) 
* IF(OR(ISNUMBER(SEARCH("S", A2)), ISNUMBER(SEARCH("W", A2))), -1, 1))

Step-by-Step Breakdown:

1. Extracting Degrees: LEFT(A2, FIND("°", A2) - 1) locates the degree symbol and grabs everything to its left.
2. Extracting Minutes: MID(A2, FIND("°", A2) + 1, FIND("'", A2) - FIND("°", A2) - 1) extracts the characters sitting between the degree symbol (°) and the single quote/minute symbol ('), then divides that value by 60.
3. Extracting Seconds: MID(A2, FIND("'", A2) + 1, FIND(CHAR(34), A2) - FIND("'", A2) - 1) extracts the text between the minute symbol and the double-quote/seconds symbol. Note: We use CHAR(34) to safely reference the double-quote mark in Excel without breaking formula syntax. This value is divided by 3600.
4. Applying Hemisphere Direction: IF(OR(ISNUMBER(SEARCH("S", A2)), ISNUMBER(SEARCH("W", A2))), -1, 1) checks if the raw string contains "S" or "W". If true, it multiplies the entire calculated decimal degree by -1 to properly place the coordinate in the Southern or Western hemisphere.

Scenario 3: Cleaning Non-Standard Spaces and Encoding Issues

Sometimes, copy-pasted coordinates contain hidden web characters or non-breaking spaces (HTML code  ) that cause standard FIND formulas to return a #VALUE! error. Before running conversion formulas, you can run a pre-cleaning formula on your data.

The Pre-Cleaning Formula

To remove leading, trailing, and invisible double-spaces, run your raw data through this formula in an adjacent column:

=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))

This formula replaces common non-breaking space characters (CHAR(160)) with a standard space, strips out non-printable ASCII characters via CLEAN, and eliminates duplicate spacing using TRIM. You can then reference this clean cell in your coordinate conversion formulas.

Pro-Tip: Standardizing the Degree Symbol

Depending on the source system, your data might use different variations of the degree symbol. The standard degree symbol is ASCII character 176 (°), but sometimes systems output a masculine ordinal indicator (ASCII 186: º).

If your Excel formulas are throwing errors, it is likely because the degree symbol in your data does not match the degree symbol typed in your formula. To bypass this, replace "°" in any formula with CHAR(176). For example:

=FIND(CHAR(176), A2)

Using CHAR(176) guarantees that Excel will find the true standard degree symbol regardless of your keyboard settings.

An Alternative: Cleaning Coordinates Fast with Power Query

If you have tens of thousands of rows, complex nested formulas can slow down your workbook. Excel's built-in Power Query tool is highly efficient for data cleaning tasks like this.

1. Select your data range and go to the Data tab, then click From Table/Range to load your coordinates into Power Query.
2. Right-click the coordinate column and choose Replace Values. Find the degree symbol (°) and replace it with nothing. Repeat this step for minutes (') and seconds (").
3. Use the Split Column by Delimiter feature (using spaces as delimiters) to separate your degrees, minutes, and seconds into distinct columns.
4. Convert the columns to decimal data types and add a custom column using the standard mathematical formula: [Degrees] + ([Minutes] / 60) + ([Seconds] / 3600).
5. Click Close & Load to return your perfectly cleaned, formatted coordinates back to your Excel worksheet.

Summary

Cleaning geographic coordinate data in Excel does not have to be a manual, tedious task. By utilizing nested text functions like SUBSTITUTE, FIND, MID, and LEFT, you can programmatically extract geographic coordinates, strip away annoying degree symbols, and convert DMS data directly into standard decimal formats. This ensures your datasets are clean, unified, and immediately ready to be mapped or analyzed.