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.

Download Practice Workbook

Download the practice workbook below.

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

To convert decimal coordinates to degree minutes and second 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.

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

• Then, select cell D5.
• Write down the following formula.

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

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

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

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

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 degree 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 link 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.

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

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

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