跳到主要内容

如何在Excel中将十进制度转换为分钟分秒?

有时,工作表中可能有以十进制度显示的数据列表,现在您需要将十进制度转换为度,分钟和秒格式,如以下屏幕截图所示,如何在Excel中快速进行对话?

使用VBA将十进制度数转换为度,分,秒

使用VBA将度,分,秒转换为十进制度


箭头蓝色右气泡 使用VBA将十进制度数转换为度,分,秒

请按照以下步骤使用VBA代码将十进制度数转换为度,分和秒。

1.按住 ALT 按钮并按下 F11 在键盘上打开一个 Microsoft Visual Basic应用程序 窗口。

2。 点击 插页 > 模块,然后将VBA复制到模块中。

VBA:将十进制度转换为度,分钟和秒

Sub ConvertDegree()
'Update 20130815
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    num1 = Rng.Value
    num2 = (num1 - Int(num1)) * 60
    num3 = Format((num2 - Int(num2)) * 60, "00")
    Rng.Value = Int(num1) & "°" & Int(num2) & "'" & Int(num3) & "''"
Next
End Sub

3。 点击 运行 按钮或按下 F5 运行VBA。

4.屏幕上显示一个对话框,您可以选择要转换的单元格。 看截图:

5。 点击 OK,然后将所选数据转换为度,分钟和秒。 看截图:

Tips::使用上述VBA代码将丢失原始数据,因此最好在运行代码之前复制数据。


箭头蓝色右气泡 使用VBA将度,分,秒转换为十进制度

有时,您可能希望将以度/分/秒为单位的数据格式转换为十进制度,下面的VBA代码可以帮助您快速完成。

1.按住 ALT按钮并按 F11 在键盘上打开“ Microsoft Visual Basic for Application”窗口。

2。 点击 插页 > 模块,然后将VBA复制到模块中。

VBA:将度,分和秒转换为十进制度

Function ConvertDecimal(pInput As String) As Double
'Updateby20140227
Dim xDeg As Double
Dim xMin As Double
Dim xSec As Double
xDeg = Val(Left(pInput, InStr(1, pInput, "°") - 1))
xMin = Val(Mid(pInput, InStr(1, pInput, "°") + 2, _
             InStr(1, pInput, "'") - InStr(1, pInput, _
             "°") - 2)) / 60
xSec = Val(Mid(pInput, InStr(1, pInput, "'") + _
            2, Len(pInput) - InStr(1, pInput, "'") - 2)) _
            / 3600
ConvertDecimal = xDeg + xMin + xSec
End Function

3.保存代码并关闭窗口,选择一个空白单元格,例如Cell A1,输入此公式 = ConvertDecimal(“ 10°27'36”“”) (“ 10°27'36”“”代表要转换为十进制的度,您可以根据需要进行更改),然后单击 输入 按钮。 查看屏幕截图:


相关文章

Comments (16)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
WRONG calculation results... 29°30'13" 34°55'4" ---> 29.00083333 34.08333333
This comment was minimized by the moderator on the site
The code has a assumes there is a space after ° and '. Change the 2's to 1's in the code to solve it.
Function ConvertDecimal(pInput As String) As Double
'Updateby20140227
Dim xDeg As Double
Dim xMin As Double
Dim xSec As Double
xDeg = Val(Left(pInput, InStr(1, pInput, "°") - 1))
xMin = Val(Mid(pInput, InStr(1, pInput, "°") + 1, _
InStr(1, pInput, "'") - InStr(1, pInput, _
"°") - 1)) / 60
xSec = Val(Mid(pInput, InStr(1, pInput, "'") + _
1, Len(pInput) - InStr(1, pInput, "'") - 1)) _
/ 3600
ConvertDecimal = xDeg + xMin + xSec
End Function
This comment was minimized by the moderator on the site
Works like a champ! Thanks for sharing!!!
This comment was minimized by the moderator on the site
Check your results. this code is wrong.
This comment was minimized by the moderator on the site
please i want you help and write a computer program on paper to convert decimal of degree to degree minute and second. 317.5986740026 from ahans.
This comment was minimized by the moderator on the site
step 1, Multiply numbers after decimal with 60 (0.5986740026*60)minutes=35.92044015 mean 35 mints
spet 2, multiply the decimal after minute ie 0.9204401*60=55.2264
so 317 degree 35 minuts 55 sec
This comment was minimized by the moderator on the site
Not sure whats wrong with the code in the post from Ernie, but this is what I use to convert decimal degrees to DMS degrees in the spread sheet without using VB. The reference to the cell E33 is the cell containing the decimal degrees value. =CONCAT(FIXED(INT(B32), 0) , "°", FIXED(((B32 - INT(B32)) * 60), 0), "'", FIXED(((B32 - INT(B32) - INT(B32 - INT(B32))) * 3600), 0 ,TRUE), """) Good luck.
This comment was minimized by the moderator on the site
HI CORNY, NOT WORK FOR ME, EXCEL DO NOT ACCEPT THE FUNCTION..... WHATS WRONG? CAN YOU HELP ME?
This comment was minimized by the moderator on the site
Hello, I used the scrip to convert decimal to DMS however the code is wrong somewhere as it turned out 37.856908,-120.912469 to 37D 51M 25S, -121D 5M 15S This is way off.... Anyone have a fix for this?
This comment was minimized by the moderator on the site
works great but how would i adapt the code to get more numbers for seconds?
This comment was minimized by the moderator on the site
I want to plot the positions on a map and we not worry about N, S, E or W. I'm sure these could be added in with "if" statements. I have just used a very simple line of functions within the Excel spreadsheet. I want to go from Degrees (Decimal) to Degrees, Seconds (Decimal) Changing the negative values to positives as I know which hemispheres I am in. For me, cell E4 contained the target Lat or Long (Decimal). This is a bit easier than putting in some VBA code and can be enlarged to do DD,MM,SS =CONCATENATE(TRUNC(ABS(E4)),"°",FIXED((ABS(E4)-TRUNC(ABS(E4)))*60,2),"'")
This comment was minimized by the moderator on the site
Found this code very helpful. Great stuff!
This comment was minimized by the moderator on the site
The most important thing to realize is that you cannot simply use superscript and small letter o for the degree symbol - this may be obvious to some, but not to me. I tried the superscript letter o and it did not work, I deleted the superscript letter o, and used Alt 0176 for the actual degree symbol and it worked!! I am confused as to why after the seconds there are extra sets of double quotes? 36""") whereas in the subsequent comment if one simply uses =ConvertDecimal(A1) only a single set of quotes are used for seconds. Odd we can use single and double quotes and the thing works for minutes and seconds but superscript letter o does not? To check 27/60 = .45 smf 36/3600 = .01 add the two = .46 and add that to the degrees = 10.46 degrees. Hope this helps others? Mort
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations