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

Get FREE Advanced Excel Exercises with Solutions!

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.

excel latitude longitude converter

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.

Convert Latitude Longitude Degree, Minute, and Second to Decimal Values for making 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.

step 2

  • 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.

step 3

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

Convert Latitude Degree, Minute, and Second to Decimal Values for making Excel latitude longitude converter

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.

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.

step 2

Then, you will reach the final stage.

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

skip column

  • 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.

Convert Latitude Longitude Degree, Minute, and Second to Decimal Values for making Excel latitude longitude converter

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.

Omitting symbols to Decimal Values for making Excel latitude longitude converter


Step-03: Applying Formulas

Now, we will calculate the decimal values.

  • Type the following formula in cell F4.
=C4+D4/60+E4/3600

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

Applying formulas to Decimal Values for making Excel latitude longitude converter

  • 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.

=C13+D13/60+E13/3600

Convert Latitude Longitude Degree, Minute, and Second to Decimal Values for making Excel latitude longitude converter


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.

Converting Latitude and Longitude Decimal Values to Degree, Minute, Second to create 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.

replace symbols

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.

Using Text to Columns feature to create Excel Latitude Longitude converter

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.

final step

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

Separating values to create Excel Latitude Longitude converter


Step-03: Using Formulas

  • Now, we will use the following formula to convert the remnant values of the Rem 1 column into minutes.
=D4*60

Using Formulas to create Excel Latitude Longitude converter

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 (,.)

replace

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

using text to columns feature to create Excel Latitude Longitude converter

  • 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.
=G4*60

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

=CONCATENATE(C4,"°"," ",D4,"'"," ",F4,"""")

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

formula

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

Later, you will have your desired DMS format.

Converting Latitude and Longitude Decimal Values to Degree, Minute, Second to create Excel Latitude Longitude converter

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.

convert to DMS format to create Excel Latitude Longitude converter

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.

practice


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo