Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


Download Practice Workbook


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.


2 Examples to Create Latitude Longitude Converter in Excel

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


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, sec, and make 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

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

Read More: Convert Latitude and Longitude to Decimal Degrees in Excel


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

Read More: How to Write Latitude and Longitude in Excel (2 Easy Ways)


Practice Section

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

practice


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.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo