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.

`=(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**.

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

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

**Read More: ****How to Convert Minutes to Seconds in Excel (2 Quick Ways)**

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

`=Convert_Deg(B5)`

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

**🔎 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.

**Read More: ****Convert Seconds to Hours and Minutes in Excel (4 Easy Methods)**

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

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

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

**🔎 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.

**Read More: ****How to Convert Seconds to Hours Minutes Seconds in Excel**

**Similar Readings**

**How to Convert Minutes to Hundredths in Excel (3 Easy Ways)****Convert Minutes to Days in Excel (3 Easy Methods)****How to Convert Hours to Percentage in Excel (3 Easy Methods)**

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

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

**🔎 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.

**Read More: ****How to Convert Seconds to Minutes in Excel**

### 5. Using Custom Formatting

You can convert decimal coordinates to degree 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\"""`

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

**Read More: ****How to Convert Minutes to Hours and Minutes 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)`

- Press
**Enter**to apply the formula.

- Then, drag the
**Fill Handle**icon down the column.

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

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

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

**Read More: ****Excel Convert Seconds to hh mm ss (7 Easy Ways)**

## 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 possible areas of this topic. If you have further questions, feel free to ask in the comment box. Don’t forget to visit our **Exceldemy** page.