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

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

### 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)`

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

### 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\""")`

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

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

### 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\"""`

- Then, click on
**OK**. - As a consequence, we will get the required conversion of decimal coordinates to degrees, minutes, and 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)`

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

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