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

End Sub

### Method 5 – Using Custom Formatting

Steps

• Select cell C5.
• Use the following formula.
=B5/24

• Press Enter to apply the formula.

• Drag the Fill Handle icon down the column.

• Select the range of cells C5 to C13.

• Go to the Home tab on the ribbon.
• Select the anchor arrow at the bottom right in the NumberÂ group.

• The Format Cells dialog box will appear.
• Select Custom from the CategoryÂ section.
• Insert the following in the TypeÂ section.
[h]Â°mm'ss\"""

• Click on OK.
• This will convert the number to the required format.

### Method 6 – Utilizing the INT Function

Steps

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

• Select cell C5.
• Use the following formula.
=INT(B5)

• Press Enter to apply the formula.

• Drag the Fill Handle icon down the column.

• Select cell D5.
• Insert the following formula.
=(B5-C5)*60

• Press Enter to apply the formula.

• Drag the Fill Handle icon down the column.

• Select cell E5.
• Insert the following formula.
=INT(D5)

• Press Enter to apply the formula.

• Drag the Fill Handle icon down the column.

• Select cell F5.
• Input the following formula.
=(D5-E5)*60

• Press Enter to apply the formula.

• Drag the Fill Handle icon down the column.

<< Go Back to Geocoding in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF