Convert Decimal Coordinates to Degrees Minutes Seconds in Excel

Get FREE Advanced Excel Exercises with Solutions!

Sometimes you have some decimal coordinates in your Excel spreadsheet and you need to convert them to degrees minutes or seconds in Excel. In Microsoft Excel, you can easily convert them using VBA code and several Excel functions. This article will show how to convert decimal coordinates to degrees minutes and seconds in Excel. I think you find this article informative and gain lots of knowledge regarding the topic.


Convert Decimal Coordinates to Degrees Minutes Seconds in Excel: 6 Suitable Methods

To convert decimal coordinates to degrees minutes and seconds in Excel, we have found six different and effective methods to use. All of these methods are fairly easy to use. To convert the decimal coordinates, we would like to utilize several Excel functions and commands. We will also use two different VBA codes to solve the problem. To show all the methods properly, we take a dataset that includes several decimal coordinates.


1. Using Text to Columns Feature

Our first method is based on using the text-to-column feature. In this method, we would like to convert decimal degrees into degrees, minutes, and seconds. In every case, we will utilize text to column feature. To understand the method carefully, follow the steps.

Steps

  • First, take a dataset that includes several columns including decimal degrees, minutes, seconds, and degrees.

  • Copy the range of cells B5 to B13 and paste it into column C.
  • Then, select the range of cells C5 to C13.

  • After that, go to the Data tab on the ribbon.
  • Then, select the Text to Columns option from the Data Tools group.

  • Then, the Convert Text to Column Wizard dialog box will appear where you need to finish 3 steps.
  • In the first step, select the Fixed Width option.
  • Then, select Next.

  • In the 2nd step, click on the point after 42. It will create a line.
  • Then, select Next.

Using Text to Columns Feature to Convert Decimal Coordinates to Degrees Minutes Seconds in Excel

  • In the third step, you will see the numbers before the line turns black.
  • Then, click on Finish.

  • As you have data in column C, you need to replace them.
  • Click on OK.

  • There we have the degrees from the decimal degrees. See the screenshot.
  • Remove the available date from Column D.

Utilizing Text to Columns Feature to Convert Decimal Coordinates to Degrees Minutes Seconds in Excel

  • Then, select cell D5.
  • Write down the following formula.
=(B5-C5)*60

  • Press Enter to apply the formula.

  • Then, drag the Fill Handle icon down the column.

  • After that, copy the range of cells D5 to D13.
  • Then, go to the Home tab on the ribbon.
  • Select the Paste drop-down option from the Clipboard group.

  • The Paste Special dialog box will appear.
  • Then, select Values from the Paste section.
  • Finally, click on OK.

  • It will convert the formula into values.
  • It is necessary because the Text to Column feature takes the formula instead of the value if there is any formula.
  • Then, select the range of cells D5 to D13.

Applying Text to Columns Feature to Convert Decimal Coordinates to Degrees Minutes Seconds in Excel

  • After that, go to the Data tab on the ribbon.
  • Then, select the Text to Columns option from the Data Tools group.

  • Then, the Convert Text to Column Wizard dialog box will appear where you need to finish 3 steps.
  • In the first step, select the Fixed Width option.
  • Then, select Next.

  • In the 2nd step, click on the point after 19. It will create a line.
  • Then, select Next.

  • In the third step, you will see the numbers before the line turns black.
  • Then, click on Finish.

  • As you have data in column D, you need to replace them.
  • Click on OK.

  • There we have the Minutes from the decimal degrees. See the screenshot.

Exercising Text to Columns Feature to Convert Decimal Coordinates to Degrees Minutes Seconds in Excel

  • Then, create a new column beside the Minutes column.
  • After that, cut the values from the Seconds column and paste them into the newly created Fraction column.

  • Then, select cell F5.
  • Write down the following formula.
=E5*60

  • Press Enter to apply the formula.

  • Then, drag the Fill Handle icon down the column.

  • This is the required conversion of decimal coordinates to degrees minutes and seconds in Excel.

Managing Text to Columns Feature to Convert Decimal Coordinates to Degrees Minutes Seconds in Excel


2. Applying User-Defined Function

Our second method is based on applying the user-defined function. In this method, we will include a VBA code where we utilize the function. Then, we will apply the function in the worksheet to convert decimal coordinates to degrees, minutes, and seconds in Excel. Before doing anything, you need to enable the Developer tab on the ribbon. To show the developer tab in the ribbon, follow the article “How to Show the Developer Tab on the Ribbon”. Follow the steps carefully to have a better understanding.

Steps

  • First, go to the Developer tab on the ribbon.
  • Then, select the Visual Basic option from the Code group.

  • It will open up the Visual Basic window.
  • Then, go to the Insert tab at the top.
  • After that, select the Module option.

  • As a result, a Module code window will appear.
  • Write down the following code.
Function Convert_Deg(Decimal_Deg) As Variant
With Application
Deg = Int(Decimal_Deg)
Min = (Decimal_Deg - Deg) * 60
Sec = Format(((Min - Int(Min)) * 60), "0")
Convert_Deg = " " & Deg & "° " & Int(Min) & " ' " & Sec + Chr(34)
End With
End Function
  • Then, close the Visual Basic window.
  • Select cell C5.
  • Then, write down the following formula.
=Convert_Deg(B5)

Applying User-Defined Function to Convert Decimal Coordinates to Degrees Minutes Seconds in Excel

  • Press Enter to apply the formula.

  • After that, drag the Fill Handle icon down the column.
  • This is the required conversion of decimal coordinates to degrees minutes and seconds in Excel.

Using User-Defined Function to Convert Decimal Coordinates to Degrees Minutes Seconds in Excel

🔎 VBA Code Explanation:

Function Convert_Deg(Decimal_Deg) As Variant

First of all, define the function name of the macro.

With Application
Deg = Int(Decimal_Deg)
Min = (Decimal_Deg - Deg) * 60
Sec = Format(((Min - Int(Min)) * 60), "0")
Convert_Deg = " " & Deg & "° " & Int(Min) & " ' " & Sec + Chr(34)
End With

Then, define the degree, minutes, and seconds equation through which it will convert the decimal degree. After that, define the function and its output. So, if you write Convert_Deg and under this, include any decimal degree, it will convert the decimal degree. Then, finish the application.

End Function

Finally, end the function of the macro.


3. Combination of TEXT and MOD Functions

Our third method is based on using the combination of TEXT and MOD functions. In this method, we will utilize the MOD function to get the value. Then, using this return value, we would like to utilize the TEXT function to convert decimal coordinates to degrees, minutes, and seconds in Excel. To understand the method properly, follow the steps.

Steps

  • First, select cell C5.
  • Then, write down the following formula in the formula box.
=TEXT(MOD(B5,360)/24,"[h]\°mm'ss\""")

Combination of TEXT and MOD Functions to Convert Decimal Coordinates to Degrees Minutes Seconds in Excel

  • Press Enter to apply the formula.

  • After that, drag the Fill Handle icon down the column.
  • This is the required conversion of decimal coordinates to degrees minutes and seconds in Excel.

Using TEXT and MOD Functions to Convert Decimal Coordinates to Degrees Minutes Seconds in Excel

🔎 Breakdown of the Formula:

TEXT(MOD(B5,360)/24,”[h]\°mm’ss\”””): First, we use the MOD function which returns a remainder after a number is divided by a divisor. Here, the decimal degree is B5 and is divided by 360. Then, divide the remainder by 24. The returned value will go under the TEXT function. Here, it takes the value and then, we have to set the text format. Finally, it returns the value into the given format.


4. Embedding VBA Code

Our fourth method is based on utilizing the VBA code. In this method, we will write down the required code and also explain it to have a better understanding. Using this VBA code, you can easily convert decimal coordinates to degrees minutes, and seconds in Excel.

Follow the steps carefully.

Steps

  • First, go to the Developer tab on the ribbon.
  • Then, select the Visual Basic option from the Code group.

  • It will open up the Visual Basic window.
  • Then, go to the Insert tab at the top.
  • After that, select the Module option.

  • As a result, a Module code window will appear.
  • Write down the following code.
Sub Convert_Degree2()
Dim RngX As Range
Dim WrkRng As Range
On Error Resume Next
xTitleId = “Convert Decimal Degree"
Set WrkRng = Application.Selection
Set WrkRng = Application.InputBox("Range", xTitleId, WrkRng.Address, Type:=8)
For Each RngX In WrkRng
number1 = RngX.Value
number2 = (number1 - Int(number1)) * 60
number3 = Format((number2 - Int(number2)) * 60, "00")
RngX.Value = Int(number1) & "°" & Int(number2) & "'" & Int(number3) & "''"
Next
End Sub
  • Then, close the Visual Basic window.
  • Copy the range of cells B5 to B13 and paste it into column C.
  • Then, select the range of cells C5 to C13.

  • Then, go to the Developer tab on the ribbon.
  • Select the Macros option from the Code group.

  • Then, the Macro dialog box will appear.
  • Select Convert_Degree2 from the Macro name section.
  • After that, click on Run.

Embedding VBA Code to Convert Decimal Coordinates to Degrees Minutes Seconds in Excel

  • Then, the Convert Decimal Degree dialog box will appear.
  • Here, the range from C5 to C13 was selected previously.
  • Finally, click on OK.

  • This is the required conversion of decimal coordinates to degrees minutes and seconds in Excel.

Applying VBA Code to Convert Decimal Coordinates to Degrees Minutes Seconds in Excel

🔎 VBA Code Explanation:

Sub Convert_Degree2()

First of all, provide a name for the sub-procedure of the macro

Dim RngX As Range
Dim WrkRng As Range

Next, declare the necessary variable for the macro.

On Error Resume Next
xTitleId = “Convert Decimal Degree"

After that, set a title that will appear to define the range.

Set WrkRng = Application.Selection
Set WrkRng = Application.InputBox("Range", xTitleId, WrkRng.Address, Type:=8)

Then, set the working range to select and create an input box to include the range of cells.

For Each RngX In WrkRng
num1 = RngX.Value
num2 = (num1 - Int(num1)) * 60
num3 = Format((num2 - Int(num2)) * 60, "00")
RngX.Value = Int(num1) & "°" & Int(num2) & "'" & Int(num3) & "''"
Next

After that, create a for each loop where we set the formula for 3 numbers. Finally, we set the output value by getting all 3 numbers in place. Then, go to the next cell and do the same procedure.

End Sub

Finally, end the sub-procedure of the macro.


5. Using Custom Formatting

You can convert decimal coordinates to degrees minutes and seconds in Excel by applying custom formatting. First, you need to divide the decimal degree by 24 and then convert it into degrees minutes, and seconds by using custom formatting. Follow the steps.

Steps

  • First, select cell C5.
  • Then, write down the following formula.
=B5/24

  • Press Enter to apply the formula.

  • Then, drag the Fill Handle icon down the column.

  • After that, select the range of cells C5 to C13.

  • Then, go to the Home tab on the ribbon.
  • Select a small tilted arrow at the right bottom. from the Number group.

  • As a result, the Format Cells dialog box will appear.
  • Then, select Custom from the Category section.
  • After that, write down the following in the Type section.
[h]°mm'ss\"""

Using Custom Formatting to Convert Decimal Coordinates to Degrees Minutes Seconds in Excel

  • Then, click on OK.
  • As a consequence, we will get the required conversion of decimal coordinates to degrees, minutes, and seconds in Excel.

Utilizing Custom Formatting to Convert Decimal Coordinates to Degrees Minutes Seconds in Excel


6. Utilizing INT Function

We can utilize the INT function to convert decimal coordinates to degrees minutes seconds in Excel. The INT function helps to round the number to the nearest integer. Using this function, we will convert decimal degrees. Follow the steps.

Steps

  • First, take a dataset that includes several columns including decimal degrees, minutes, seconds, and fractions.

  • Then, select cell C5.
  • Write down the following formula.
=INT(B5)

Exercising INT Function to Convert Decimal Coordinates to Degrees Minutes Seconds in Excel

  • Press Enter to apply the formula.

  • Then, drag the Fill Handle icon down the column.

Utilizing INT Function to Convert Decimal Coordinates to Degrees Minutes Seconds in Excel

  • Then, select cell D5.
  • Write down the following formula.
=(B5-C5)*60

Managing INT Function to Convert Decimal Coordinates to Degrees Minutes Seconds in Excel

  • Press Enter to apply the formula.

  • Then, drag the Fill Handle icon down the column.

  • Then, select cell E5.
  • Write down the following formula.
=INT(D5)

  • Press Enter to apply the formula.

  • Then, drag the Fill Handle icon down the column.

Using INT Function to Convert Decimal Coordinates to Degrees Minutes Seconds in Excel

  • Then, select cell F5.
  • Write down the following formula.
=(D5-E5)*60

  • Press Enter to apply the formula.

  • Then, drag the Fill Handle icon down the column.
  • This is the required conversion of decimal coordinates to degrees minutes and seconds in Excel.

Applying INT Function to Convert Decimal Coordinates to Degrees Minutes Seconds in Excel


Download Practice Workbook

Download the practice workbook below.


Conclusion

We have shown six different and efficient methods to convert decimal coordinates to degrees, minutes, or seconds in Excel. This article uses several Excel commands, functions, and VBA codes to solve the issue. I hope we covered as many possible areas of this topic. If you have further questions, feel free to ask in the comment box.


<< 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.
Durjoy Paul
Durjoy Paul

Durjoy Kumar, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, is a dedicated contributor to the ExcelDemy project. His substantial contributions include crafting numerous articles and demonstrating expertise in Excel and VBA. Durjoy adeptly automates Excel challenges using VBA macros, offering valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, SPSS, C, C++, C#, JavaScript, Python Web Scraping, Data Entry... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo