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

If you are looking for a latitude-longitude converter in Excel with which you can convert DMS values to Decimal values, then this article will be helpful for you. So, let’s get into the main article.

What Is Latitude Longitude?

Latitude-longitude is basically two numbers to represent the position of a place in the geographical coordinate system. It is generally represented in two formats; Degree-Minute-Second (DMS), and Decimal. The values are negative in the South and West positions and positive in the North and East positions.

Excel Latitude Longitude Converter: 2 Examples

In the following dataset, we have some latitude and longitude values of some random places. In the following two examples, we will show the process of converting DMS to Decimal and Decimal to DMS.

For creating this article, we have used the Microsoft Excel 365 version. You can use any other version.

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

Here, we have the latitudes and longitudes in DMS format, and so we will convert them into decimal values to create an Excel latitude-longitude converter.

Step-01: Using Text to Columns Option

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

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

Now, we will separate the degrees, minutes, and seconds in these new columns.

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

Afterward, the Convert Text to Columns Wizard will open up.

• Choose the Delimited option, and press Next.

Then, you will get into the next stage of this wizard.

• Select Space as the Delimiter, and click on Next.

• In the final stage, choose the Column data format as General.
• Enter the destination cell where you want the output (here, we have entered $C$4), and press Finish.

In this way, we have separated the degree, minute, and second values into three different columns.

Following this procedure, we have also separated the values of the longitudes.

Step-02: Omitting Symbols

Here, we will remove the symbols from the degree, minute, and second values.

• To omit the symbol of the degree, minute, and second, here we have entered 3 additional columns again.

Let’s see how to omit the degree symbol first.

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

Afterward, the Convert Text to Columns Wizard will open up.

• Choose the Delimited option, and press Next.

Then, you will get into the next stage of this wizard.

• Select Other as the Delimiter, and put down ° (degree).
• Click on Next.

Then, you will reach the final stage.

• 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 (here, we have entered $D$4), and press Finish.

Later, we extracted the values only from the deg column.

Similarly, extract the minute and second values in the Min, and Sec columns.

Following this procedure, we have also removed the degree, minute, and second symbols for the longitude coordinates also.

Afterward, we deleted the unnecessary columns; deg, min, and sec, and made sure to enter the minus (-) symbol before the values of degrees, minutes, and seconds when the latitude is negative.

Step-03: Applying Formulas

Now, we will calculate the decimal values.

• Type the following formula in cell F4.

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

• Press ENTER, and drag down the Fill Handle.

Finally, you will have the following decimal values of latitude coordinates.

Similarly, 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

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

Step-01: Replace Symbols

Firstly, we will 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.

Later, the Find and Replace dialog box will open up.

• Go to the Replace tab, and write the following

Find what → ,
Replace with → ,.

• Choose to Replace All.

In this way, we have replaced commas with ,. to facilitate the next step.

Step-02: Using Text to Columns Feature

Here, we will 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.

Afterward, the Convert Text to Columns Wizard will open up.

• Choose the Delimited option, and press Next.

Then, you will get into the next stage of this wizard.

• Select Comma as the Delimiter, and click on Next.

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

In this way, we have split the values before and after the decimal points.

Step-03: Using Formulas

• Now, we will 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, we have pasted these results as values in the Rem 2 column.

• Then, using the previous procedure we have replaced the comma(,) with a comma & full stop (,.)

After that, using the Text to Columns option again we separated the values before and after the decimal points.

• In the Rem 3 column, we got the second value which we have to multiply by 60, and so we have used the following formula in the Sec column.

Finally, to have our desired format of DMS values, we have used the following formula in cell I4.

Here, the CONCATENATE function will combine the values with their respective symbols.

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

Later, you will have your desired DMS format.

Now, we will repeat all of the procedures for latitude decimal coordinates again to convert the following longitudinal coordinates.

Eventually, we obtained the longitude coordinates in DMS format.

After completing the tasks, we removed the unnecessary columns.

Practice Section

For doing practice, we attached a Practice section on the right side of each sheet.

Download Practice Workbook

Conclusion

In this article, we tried to create a latitude-longitude converter in Excel in two ways. Hope you will find it useful. For any further queries, you can leave a comment below.

<< Go Back to Geocoding in Excel | Learn Excel