Convert Decimal Coordinates to Degrees Minutes Seconds in Excel

Method 1 – Using the Text to Columns Feature

Steps

Make a dataset that includes several columns including decimal degrees and separate minutes, seconds, and degrees.

Copy the range of cells B5 to B13 and paste it into column C.
Select the range of cells C5 to C13.

Go to the Data tab on the ribbon.
Select the Text to Columns option from the Data Tools group.

The Convert Text to Column Wizard dialog box will appear where you need to finish 3 steps.
Select the Fixed Width option.
Select Next.

Click on the point after 42. It will create a line.
Select Next.

You will see the numbers before the line turn black.
Click on Finish.

You’ll get a warning box. Click on OK.

We get the degrees from the decimal degrees. See the screenshot.
Remove the available data from Column D.

Select cell D5.
Copy the following formula.

=(B5-C5)*60

Press Enter to apply the formula.

Drag the Fill Handle icon down the column.

Copy the range of cells D5 to D13.
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.
Select Values from the Paste section.
Click on OK.

This will convert the formula into values. The Text to Column feature takes the formula instead of the value if there is any formula, so we’re bypassing that.
Select the range of cells D5 to D13.

Go to the Data tab on the ribbon.
Select the Text to Columns option from the Data Tools group.

The Convert Text to Column Wizard dialog box will appear.
Select the Fixed Width option.
Select Next.

Click on the point after 19. It will create a line.
Select Next.

You will see the numbers before the line go black.
Click on Finish.

Click on OK in the dialog.

We have the Minutes from the decimal degrees. See the screenshot.

Create a new column beside the Minutes column.
Cut the values from the Seconds column and paste them into the newly created Fraction column.

Select cell F5.
Use the following formula.

=E5*60

Press Enter to apply the formula.

Drag the Fill Handle icon down the column.

Here’s the final result.

Method 2 – Applying a User-Defined Function

For this method, you may need to enable the Developer tab on the ribbon. Follow the article “How to Show the Developer Tab on the Ribbon” to enable it if needed.

Steps

Go to the Developer tab on the ribbon.
Select the Visual Basic option from the Code group.

It will open up the Visual Basic window.
Go to the Insert tab at the top.
Select the Module option.

A Module code window will appear.
Copy the following code into it.

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

Close the Visual Basic window.
Select cell C5.
Insert the following formula.

=Convert_Deg(B5)

Press Enter to apply the formula.

Drag the Fill Handle icon down the column.
This completes the conversion.

VBA Code Explanation:

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

We defined the degree, minutes, and seconds equation which will convert the decimal degree, and put that into the result string.

End Function

Method 3 – Combination of TEXT and MOD Functions

Steps

Select cell C5.
Insert the following formula in the formula box.

=TEXT(MOD(B5,360)/24,"[h]\°mm'ss\""")

Press Enter to apply the formula.

Drag the Fill Handle icon down the column.
Here’s the final result.

Breakdown of the Formula:

TEXT(MOD(B5,360)/24,”[h]\°mm’ss\”””): The MOD function returns a remainder after a number is divided by a divisor. Here, the decimal degree is B5 and is divided by 360. Then, we 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.

Method 4 – Embedding VBA Code

Steps

Go to the Developer tab on the ribbon.
Select the Visual Basic option from the Code group.

This will open the Visual Basic window.
Go to the Insert tab at the top.
Select the Module option.

A Module code window will appear.
Insert 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

Close the Visual Basic window.
Copy the range of cells B5 to B13 and paste it into column C.
Select the range of cells C5 to C13.

Go to the Developer tab on the ribbon.
Select the Macros option from the Code group.

The Macro dialog box will appear.
Select Convert_Degree2 from the Macro name section.
Click on Run.

The Convert Decimal Degree dialog box will appear.
The range from C5:C13 was selected automatically, but you may need to insert it.
Click on OK.

Here’s the result.

VBA Code Explanation:

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)

We 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

The function uses a for each loop where we set the formula for 3 numbers. We set the output value by getting all 3 numbers in place. Then, the function goes to the next cell and repeats the same procedure.