How to Create Latitude Longitude Converter in Excel (2 Examples)

In the following two examples, we’ll use the sample dataset below to show the process of converting DMS to Decimal and Decimal to DMS.

Example 1 – Converter of Latitude Longitude Degree, Minute, and Second to Decimal Values

The latitudes and longitudes are in DMS format. We will convert them into decimal values to create an Excel latitude-longitude converter.

Step 1 – Using Text to Columns Option

We will use the Text to Columns option to separate the degrees, minutes, and seconds.

• Enter 3 additional temporary columns – deg, min, sec.

Separate the degrees, minutes, and seconds in these new columns.

• Select the range, and go to the Data tab >> Text to Columns.

• In the Convert Text to Columns Wizard pop-up window, choose the Delimited option, click Next.

• Select Space as the Delimiter, and click Next.

• Choose the Column data format as General.
• Enter the destination cell where you want the output (here, we have entered $C$4) and click Finish.

The degree, minute and second values will be separated into three different columns.

Separate the values of the longitudes using the same procedure.

Step 2 – Omitting Symbols

• To omit the degree, minute, and second symbols, insert 3 additional columns.

• Select the range of the deg column, go to the Data tab >> Text to Columns.

In the Convert Text to Columns Wizard, choose the Delimited option and click Next.

• Select Other as the Delimiter and input ° (degree).
• Click Next.

• Select the second column from Data Preview and click on the Do not import column (skip).

• Enter the destination cell where you want the output (we have entered $D$4), and press Finish.

Extract the values only from the deg column.

Extract the minute and second values in the Min, and Sec columns.

Remove the degree, minute, and second symbols for the longitude coordinates following the same procedure.

After deleting the unnecessary columns (i.e. deg, min, and sec), enter the minus (-) symbol before the values of degrees, minutes, and seconds when the latitude is negative.

Step 3 – Applying Formulas

To calculate the decimal values.

• Add the following formula in cell F4.

C4 is the degree value, D4 is the minute value and E4 is the second value.

• Press ENTER and drag down the Fill Handle.

You will get the following decimal values of latitude coordinates.

Use the following formula to get the decimal values of the longitude coordinates.

Example 2 – Converting Latitude and Longitude Decimal Values to Degree, Minute, Second in Excel

We will convert the following decimal values to DMS format to make an Excel latitude-longitude converter.

Step 1 – Replace Symbols

Replace the decimal points with ,. to split the values before the decimal points and after the points.

• Select the range, and go to the Home tab >> Editing group >> Find & Select dropdown >> Replace.

In the Find and Replace dialog box, go to the Replace tab, and enter the following

Find what → ,
Replace with → ,.

• Choose to Replace All.

The commas are replaced with ,.

Step 2 – Using Text to Columns Feature

To separate the values after and before the decimal points:

• Select the range of the Lat D column and go to the Data tab >> Text to Columns.

In the Convert Text to Columns Wizard, choose the Delimited option and click Next.

• Select Comma as the Delimiter and click on Next.

• Enter the destination cell where you want the output (here, we have entered $C$4) and press Finish.

The values before and after the decimal points will be split.

Step 3 – Using Formulas

• Use the following formula to convert the remnant values of the Rem 1 column into minutes.

To extract the minutes from the values of the Rem 2 column, paste these results as values in the Rem 2 column.

• Using the previous procedure, replace the comma(,) with a comma & full stop (,.)

Use the Text to Columns option to separate the values before and after the decimal points.

• In the Sec column, use the following formula:

Use the following formula in cell I4:

The CONCATENATE function will combine the values with their respective symbols.

• Press ENTER, and drag down the Fill Handle tool.

It will output the desired DMS format.

Repeat all of the procedures for latitude decimal coordinates to convert the following longitudinal coordinates.

The longitude coordinates will be in DMS format.

Remove the unnecessary columns.

Download Practice Workbook

<< Go Back to Geocoding in Excel | Learn Excel