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

**Download the Practice Workbook**

**<< Go Back to Geocoding in Excel | Learn Excel**