excel提取数字。表格中角度输入225°32′12.4″，都输成了225.32124，想把度分秒的数字都提取出来。
答案:4 悬赏:60 手机版
解决时间 2021-11-23 04:25
- 提问者网友：不要迷恋哥
- 2021-11-22 08:40
excel提取数字。表格中角度输入225°32′12.4″，都输成了225.32124，想把度分秒的数字都提取出来。
最佳答案
- 五星知识达人网友：夜余生
- 2021-11-22 10:03
A2输入公式 =LEFt(A1,FIND(".",A1)-1)
A3输入公式 =MId(A1,FIND(".",A1)+1,2)
A4输入公式 =RIGHt(A1,LEN(A1)-FIND(".",A1)-2)追问A1单元格我是保留五位小数，但角度为90.00000，就提取不出来小数点后面数字了追答建议将A1设置为文本格式
A3输入公式 =MId(A1,FIND(".",A1)+1,2)
A4输入公式 =RIGHt(A1,LEN(A1)-FIND(".",A1)-2)追问A1单元格我是保留五位小数，但角度为90.00000，就提取不出来小数点后面数字了追答建议将A1设置为文本格式
全部回答
- 1楼网友：深街酒徒
- 2021-11-22 14:27
用宏解决，先整体判断数值的区间，0-1,1-10，10-100，决定取几位，作为度，接着用整体值减去整个值的前三位【也就是取整数】，然后判断差值的区间，0-1,1-10,10-60，作为分，最后是秒，同样的方法，写下来代码很多，呵呵，本来想粘贴上来给你参考，无奈字数有限，就贴一部分，另外提醒下，255.32124是不是应该是 255° 分要*60，然后秒*60【看你情况哦】呢，曾经我一个程序的一部分就有弧度转角度。
Sub HDtoJD()
Dim i As Integer
i = i + 1
For i = 3 To 36
Dim pi
pi = 4 * Atn(1)
Dim Degree As String
Degree = Sheets(1).Cells(i, 8).Value
Dim Minute1, Minute2, Minute3 As String
Dim Second1, Second2 As String
Dim Second10, Second12 As String
Dim Second100, Second120 As String
Dim n As String
n = Sheets(1).Cells(2, 8).Value
Minute1 = (Val(Degree) - Val(Left(Degree, 1))) * 60
Minute2 = (Val(Degree) - Val(Left(Degree, 2))) * 60
Minute3 = (Val(Degree) - Val(Left(Degree, 3))) * 60
Second1 = (Val((Val(Degree) - Val(Left(Degree, 1))) * 60) - Val(Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 1))) * 60
Second2 = (Val((Val(Degree) - Val(Left(Degree, 1))) * 60) - Val(Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 2))) * 60
Second10 = (Val((Val(Degree) - Val(Left(Degree, 2))) * 60) - Val(Left((Val(Degree) - Val(Left(Degree, 2))) * 60, 1))) * 60
Second12 = (Val((Val(Degree) - Val(Left(Degree, 2))) * 60) - Val(Left((Val(Degree) - Val(Left(Degree, 2))) * 60, 2))) * 60
Second100 = (Val((Val(Degree) - Val(Left(Degree, 3))) * 60) - Val(Left((Val(Degree) - Val(Left(Degree, 3))) * 60, 1))) * 60
Second120 = (Val((Val(Degree) - Val(Left(Degree, 3))) * 60) - Val(Left((Val(Degree) - Val(Left(Degree, 3))) * 60, 2))) * 60
If Sheets(1).Cells(i, 8).Value <> "" Then
'if Degree = ""
'///////////////////////////////////////////////////////////////////////////////////////////////////////0-1 Degree Starting
If Degree >= 0 And Degree < 1 Then
'0-1 Degree Start
If Minute1 < 1 And Minute1 >= 0 Then
'0-1 Minute Start
If Second1 < 0.01 And Second1 >= 0 Then
Sheets(1).Cells(i, 6).Value = " 0" & " " & _
" 0" & " " & _
" 0" & _
Left(Second1, 3)
End If
If Second1 < 0.1 And Second1 >= 0.01 Then
Sheets(1).Cells(i, 6).Value = " 0" & " " & _
" 0" & " " & _
" 0" & _
Left(Second1, 3)
End If
If Second1 < 1 And Second1 >= 0.1 Then
Sheets(1).Cells(i, 6).Value = " 0" & " " & _
" 0" & " " & _
" 0" & _
Left(Second1, 3)
End If
If Second1 < 10 And Second1 >= 1 Then
Sheets(1).Cells(i, 6).Value = " 0" & " " & _
" 0" & " " & _
" " & Left(Second1, 4)
End If
If Second1 < 60 And Second1 >= 10 Then
Sheets(1).Cells(i, 6).Value = " 0" & " " & _
" 0" & " " & _
Left(Second1, 5)
End If
End If
'0-1 Minute Filished
If Minute1 < 10 And Minute1 >= 1 Then
'1-10 Minute Start
If Second1 < 0.01 And Second1 >= 0 Then
Sheets(1).Cells(i, 6).Value = " 0" & " " & "0" & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 1) & " " & _
" 0" & _
Left(Second1, 3)
End If
If Second1 < 0.1 And Second1 >= 0.01 Then
Sheets(1).Cells(i, 6).Value = " 0" & " " & "0" & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 1) & " " & _
" 0" & _
Left(Second1, 3)
End If
If Second1 < 1 And Second1 >= 0.1 Then
Sheets(1).Cells(i, 6).Value = " 0" & " " & "0" & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 1) & " " & _
" 0" & _
Left(Second1, 3)
End If
If Second1 < 10 And Second1 >= 1 Then
Sheets(1).Cells(i, 6).Value = " 0" & " " & "0" & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 1) & " " & " " & _
Left(Second1, 4)
End If
If Second1 < 60 And Second1 >= 10 Then
Sheets(1).Cells(i, 6).Value = " 0" & " " & "0" & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 1) & " " & _
Left(Second1, 5)
End If
End If
'1-10 Minute Filished
If Minute1 < 60 And Minute1 >= 10 Then
'10-60 Minute Start
If Second2 < 0.01 And Second2 >= 0 Then
Sheets(1).Cells(i, 6).Value = " 0" & " " & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 2) & " " & _
" 0" & _
Left(Second2, 3)
End If
If Second2 < 0.1 And Second2 >= 0.01 Then
Sheets(1).Cells(i, 6).Value = " 0" & " " & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 2) & " " & _
" 0" & _
Left(Second2, 3)
End If
If Second2 < 1 And Second2 >= 0.1 Then
Sheets(1).Cells(i, 6).Value = " 0" & " " & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 2) & " " & _
" 0" & _
Left(Second2, 3)
End If
If Second2 < 10 And Second2 >= 1 Then
Sheets(1).Cells(i, 6).Value = " 0" & " " & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 2) & " " & " " & _
Left(Second2, 4)
End If
If Second2 < 60 And Second2 >= 10 Then
Sheets(1).Cells(i, 6).Value = " 0" & " " & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 2) & " " & _
Left(Second2, 5)
End If
End If
'10-60Minute filished
End If
'0-1 Degree Filished
'///////////////////////////////////////////////////////////////////////////////////////////////////////0-1 Degree Filished
'///////////////////////////////////////////////////////////////////////////////////////////////////////1-10 Degree Starting
If Degree >= 1 And Degree < 10 Then
'1-10 Degree Start
If Minute1 < 1 And Minute1 >= 0 Then
'0-1 Minute Start
'///////处理由于科学计数产生的负数Started
If Minute1 < 0.01667 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 1) & " " & _
" 0" & " " & _
" 0" & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 3600, 3)
End If
If Minute1 >= 0.01667 And Minute1 < 0.1 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 1) & " " & _
" 0" & " " & _
" " & Left((Val(Degree) - Val(Left(Degree, 1))) * 3600, 3)
End If
'///////处理由于科学计数产生的负数Filished
If Second1 < 0.1 And Second1 >= 0 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 1) & " " & _
" 0" & " " & _
" 0" & _
Left(Second1, 3)
End If
If Second1 < 1 And Second1 >= 0.1 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 1) & " " & _
" 0" & " " & _
" 0" & _
Left(Second1, 3)
End If
If Second1 < 10 And Second1 >= 1 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 1) & " " & _
" 0" & " " & _
" " & Left(Second1, 4)
End If
If Second1 < 60 And Second1 >= 10 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 1) & " " & _
" 0" & " " & _
Left(Second1, 5)
End If
End If
'0-1 Minute Filished
If Minute1 < 10 And Minute1 >= 1 Then
'1-10 Minute Start
If Second1 < 0.01 And Second1 >= 0 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 1) & " " & "0" & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 1) & " " & _
" 0" & _
Left(Second1, 3)
End If
If Second1 < 0.1 And Second1 >= 0.01 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 1) & " " & "0" & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 1) & " " & _
" 0" & _
Left(Second1, 3)
End If
If Second1 < 1 And Second1 >= 0.1 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 1) & " " & "0" & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 1) & " " & _
" 0" & _
Left(Second1, 3)
End If
If Second1 < 10 And Second1 >= 1 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 1) & " " & "0" & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 1) & " " & _
" " & Left(Second1, 4)
End If
If Second1 < 60 And Second1 >= 10 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 1) & " " & "0" & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 1) & " " & _
Left(Second1, 5)
End If
End If
'1-10 Minute Filished
If Minute2 < 60 And Minute2 >= 10 Then
'10-60 Minute Start
If Second2 < 0.01 And Second2 >= 0 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 1) & " " & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 2) & " " & _
" 0" & _
Left(Second2, 3)
End If
If Second2 < 0.1 And Second2 >= 0.01 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 1) & " " & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 2) & " " & _
" 0" & _
Left(Second2, 3)
End If
If Second2 < 1 And Second2 >= 0.1 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 1) & " " & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 2) & " " & _
" 0" & _
Left(Second2, 3)
End If
If Second2 < 10 And Second2 >= 1 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 1) & " " & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 2) & " " & _
" " & Left(Second2, 4)
End If
If Second2 < 60 And Second2 >= 10 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 1) & " " & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 2) & " " & _
Left(Second2, 5)
End If
End If
'10-60 Minute Filished
End If
'1 -10 Degree Filished
'//////////////////////////////////////////////////////////////////////////////////////1-10 Degree Filished
'/////////////////////////////////////////////////////////////////////////////////////10-100 Degree Starting
If Degree >= 10 And Degree < 100 Then
'10-100 Degree Start
If Minute2 < 1 And Minute2 >= 0 Then
'0-1 Minute Start
'///////处理由于科学计数产生的负数Started
If Minute2 < 0.01667 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 2) & " " & _
" 0" & " " & _
" 0" & _
Left((Val(Degree) - Val(Left(Degree, 2))) * 3600, 3)
End If
If Minute2 >= 0.01667 And Minute2 < 0.1 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 2) & " " & _
" 0" & " " & _
" " & Left((Val(Degree) - Val(Left(Degree, 2))) * 3600, 3)
End If
'///////处理由于科学计数产生的负数Filished
If Second10 < 0.1 And Second10 >= 0 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 2) & " " & _
" 0" & " " & _
" 0" & _
Left(Second10, 3)
End If
If Second10 < 1 And Second10 >= 0.1 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 2) & " " & _
" 0" & " " & _
" 0" & _
Left(Second100, 3)
End If
If Second10 < 10 And Second10 >= 1 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 2) & " " & _
" 0" & " " & _
" " & Left(Second10, 4)
End If
If Second12 < 60 And Second12 >= 10 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 2) & " " & _
" 0" & " " & _
Left(Second12, 5)
End If
End If
'0-1 Minute Filished
If Minute2 < 10 And Minute2 >= 1 Then
'1-10 Minute Start
If Second10 < 0.01 And Second10 >= 0 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 2) & " " & "0" & _
Left((Val(Degree) - Val(Left(Degree, 2))) * 60, 1) & " " & _
" 0" & _
Left(Second10, 3)
End If
If Second10 < 0.1 And Second10 >= 0.01 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 2) & " " & "0" & _
Left((Val(Degree) - Val(Left(Degree, 2))) * 60, 1) & " " & _
" 0" & _
Left(Second10, 3)
End If
If Second10 < 1 And Second10 >= 0.1 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 2) & " " & "0" & _
Left((Val(Degree) - Val(Left(Degree, 2))) * 60, 1) & " " & _
" 0" & _
Left(Second10, 3)
End If
If Second10 < 10 And Second10 >= 1 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 2) & " " & "0" & _
Left((Val(Degree) - Val(Left(Degree, 2))) * 60, 1) & " " & _
" " & Left(Second10, 4)
End If
If Second12 < 60 And Second12 >= 10 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 2) & " " & "0" & _
Left((Val(Degree) - Val(Left(Degree, 2))) * 60, 1) & " " & _
Left(Second12, 5)
End If
End If
'1-10 Minute Filished
If Minute3 < 60 And Minute3 >= 10 Then
'10-60 Minute Start
If Second12 < 0.01 And Second12 >= 0 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 2) & " " & _
Left((Val(Degree) - Val(Left(Degree, 2))) * 60, 2) & " " & _
" 0" & _
Left(Second12, 3)
End If
If Second12 < 0.1 And Second12 >= 0.01 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 2) & " " & _
Left((Val(Degree) - Val(Left(Degree, 2))) * 60, 2) & " " & _
" 0" & _
Left(Second12, 3)
End If
If Second12 < 1 And Second12 >= 0.1 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 2) & " " & _
Left((Val(Degree) - Val(Left(Degree, 2))) * 60, 2) & " " & _
" 0" & _
Left(Second12, 3)
End If
If Second12 < 10 And Second12 >= 1 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 2) & " " & _
Left((Val(Degree) - Val(Left(Degree, 2))) * 60, 2) & " " & _
" " & Left(Second12, 4)
End If
If Second12 < 60 And Second12 >= 10 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 2) & " " & _
Left((Val(Degree) - Val(Left(Degree, 2))) * 60, 2) & " " & _
Left(Second12, 5)
End If
End If
'10-60 Minute Filished
End If
'10-100 Degree Flished
'/////////////////////////////////////////////////////////////////////////////////////10-100 DegreeFilished
'/////////////////////////////////////////////////////////////////////////////////////100-360 Degree Starting
以上只是部分代码，字数限制啊，后面的你可以参照前面的了。
弱弱的问一句，不会你也是搞测量的吧？哈哈
Sub HDtoJD()
Dim i As Integer
i = i + 1
For i = 3 To 36
Dim pi
pi = 4 * Atn(1)
Dim Degree As String
Degree = Sheets(1).Cells(i, 8).Value
Dim Minute1, Minute2, Minute3 As String
Dim Second1, Second2 As String
Dim Second10, Second12 As String
Dim Second100, Second120 As String
Dim n As String
n = Sheets(1).Cells(2, 8).Value
Minute1 = (Val(Degree) - Val(Left(Degree, 1))) * 60
Minute2 = (Val(Degree) - Val(Left(Degree, 2))) * 60
Minute3 = (Val(Degree) - Val(Left(Degree, 3))) * 60
Second1 = (Val((Val(Degree) - Val(Left(Degree, 1))) * 60) - Val(Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 1))) * 60
Second2 = (Val((Val(Degree) - Val(Left(Degree, 1))) * 60) - Val(Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 2))) * 60
Second10 = (Val((Val(Degree) - Val(Left(Degree, 2))) * 60) - Val(Left((Val(Degree) - Val(Left(Degree, 2))) * 60, 1))) * 60
Second12 = (Val((Val(Degree) - Val(Left(Degree, 2))) * 60) - Val(Left((Val(Degree) - Val(Left(Degree, 2))) * 60, 2))) * 60
Second100 = (Val((Val(Degree) - Val(Left(Degree, 3))) * 60) - Val(Left((Val(Degree) - Val(Left(Degree, 3))) * 60, 1))) * 60
Second120 = (Val((Val(Degree) - Val(Left(Degree, 3))) * 60) - Val(Left((Val(Degree) - Val(Left(Degree, 3))) * 60, 2))) * 60
If Sheets(1).Cells(i, 8).Value <> "" Then
'if Degree = ""
'///////////////////////////////////////////////////////////////////////////////////////////////////////0-1 Degree Starting
If Degree >= 0 And Degree < 1 Then
'0-1 Degree Start
If Minute1 < 1 And Minute1 >= 0 Then
'0-1 Minute Start
If Second1 < 0.01 And Second1 >= 0 Then
Sheets(1).Cells(i, 6).Value = " 0" & " " & _
" 0" & " " & _
" 0" & _
Left(Second1, 3)
End If
If Second1 < 0.1 And Second1 >= 0.01 Then
Sheets(1).Cells(i, 6).Value = " 0" & " " & _
" 0" & " " & _
" 0" & _
Left(Second1, 3)
End If
If Second1 < 1 And Second1 >= 0.1 Then
Sheets(1).Cells(i, 6).Value = " 0" & " " & _
" 0" & " " & _
" 0" & _
Left(Second1, 3)
End If
If Second1 < 10 And Second1 >= 1 Then
Sheets(1).Cells(i, 6).Value = " 0" & " " & _
" 0" & " " & _
" " & Left(Second1, 4)
End If
If Second1 < 60 And Second1 >= 10 Then
Sheets(1).Cells(i, 6).Value = " 0" & " " & _
" 0" & " " & _
Left(Second1, 5)
End If
End If
'0-1 Minute Filished
If Minute1 < 10 And Minute1 >= 1 Then
'1-10 Minute Start
If Second1 < 0.01 And Second1 >= 0 Then
Sheets(1).Cells(i, 6).Value = " 0" & " " & "0" & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 1) & " " & _
" 0" & _
Left(Second1, 3)
End If
If Second1 < 0.1 And Second1 >= 0.01 Then
Sheets(1).Cells(i, 6).Value = " 0" & " " & "0" & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 1) & " " & _
" 0" & _
Left(Second1, 3)
End If
If Second1 < 1 And Second1 >= 0.1 Then
Sheets(1).Cells(i, 6).Value = " 0" & " " & "0" & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 1) & " " & _
" 0" & _
Left(Second1, 3)
End If
If Second1 < 10 And Second1 >= 1 Then
Sheets(1).Cells(i, 6).Value = " 0" & " " & "0" & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 1) & " " & " " & _
Left(Second1, 4)
End If
If Second1 < 60 And Second1 >= 10 Then
Sheets(1).Cells(i, 6).Value = " 0" & " " & "0" & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 1) & " " & _
Left(Second1, 5)
End If
End If
'1-10 Minute Filished
If Minute1 < 60 And Minute1 >= 10 Then
'10-60 Minute Start
If Second2 < 0.01 And Second2 >= 0 Then
Sheets(1).Cells(i, 6).Value = " 0" & " " & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 2) & " " & _
" 0" & _
Left(Second2, 3)
End If
If Second2 < 0.1 And Second2 >= 0.01 Then
Sheets(1).Cells(i, 6).Value = " 0" & " " & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 2) & " " & _
" 0" & _
Left(Second2, 3)
End If
If Second2 < 1 And Second2 >= 0.1 Then
Sheets(1).Cells(i, 6).Value = " 0" & " " & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 2) & " " & _
" 0" & _
Left(Second2, 3)
End If
If Second2 < 10 And Second2 >= 1 Then
Sheets(1).Cells(i, 6).Value = " 0" & " " & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 2) & " " & " " & _
Left(Second2, 4)
End If
If Second2 < 60 And Second2 >= 10 Then
Sheets(1).Cells(i, 6).Value = " 0" & " " & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 2) & " " & _
Left(Second2, 5)
End If
End If
'10-60Minute filished
End If
'0-1 Degree Filished
'///////////////////////////////////////////////////////////////////////////////////////////////////////0-1 Degree Filished
'///////////////////////////////////////////////////////////////////////////////////////////////////////1-10 Degree Starting
If Degree >= 1 And Degree < 10 Then
'1-10 Degree Start
If Minute1 < 1 And Minute1 >= 0 Then
'0-1 Minute Start
'///////处理由于科学计数产生的负数Started
If Minute1 < 0.01667 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 1) & " " & _
" 0" & " " & _
" 0" & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 3600, 3)
End If
If Minute1 >= 0.01667 And Minute1 < 0.1 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 1) & " " & _
" 0" & " " & _
" " & Left((Val(Degree) - Val(Left(Degree, 1))) * 3600, 3)
End If
'///////处理由于科学计数产生的负数Filished
If Second1 < 0.1 And Second1 >= 0 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 1) & " " & _
" 0" & " " & _
" 0" & _
Left(Second1, 3)
End If
If Second1 < 1 And Second1 >= 0.1 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 1) & " " & _
" 0" & " " & _
" 0" & _
Left(Second1, 3)
End If
If Second1 < 10 And Second1 >= 1 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 1) & " " & _
" 0" & " " & _
" " & Left(Second1, 4)
End If
If Second1 < 60 And Second1 >= 10 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 1) & " " & _
" 0" & " " & _
Left(Second1, 5)
End If
End If
'0-1 Minute Filished
If Minute1 < 10 And Minute1 >= 1 Then
'1-10 Minute Start
If Second1 < 0.01 And Second1 >= 0 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 1) & " " & "0" & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 1) & " " & _
" 0" & _
Left(Second1, 3)
End If
If Second1 < 0.1 And Second1 >= 0.01 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 1) & " " & "0" & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 1) & " " & _
" 0" & _
Left(Second1, 3)
End If
If Second1 < 1 And Second1 >= 0.1 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 1) & " " & "0" & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 1) & " " & _
" 0" & _
Left(Second1, 3)
End If
If Second1 < 10 And Second1 >= 1 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 1) & " " & "0" & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 1) & " " & _
" " & Left(Second1, 4)
End If
If Second1 < 60 And Second1 >= 10 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 1) & " " & "0" & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 1) & " " & _
Left(Second1, 5)
End If
End If
'1-10 Minute Filished
If Minute2 < 60 And Minute2 >= 10 Then
'10-60 Minute Start
If Second2 < 0.01 And Second2 >= 0 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 1) & " " & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 2) & " " & _
" 0" & _
Left(Second2, 3)
End If
If Second2 < 0.1 And Second2 >= 0.01 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 1) & " " & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 2) & " " & _
" 0" & _
Left(Second2, 3)
End If
If Second2 < 1 And Second2 >= 0.1 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 1) & " " & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 2) & " " & _
" 0" & _
Left(Second2, 3)
End If
If Second2 < 10 And Second2 >= 1 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 1) & " " & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 2) & " " & _
" " & Left(Second2, 4)
End If
If Second2 < 60 And Second2 >= 10 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 1) & " " & _
Left((Val(Degree) - Val(Left(Degree, 1))) * 60, 2) & " " & _
Left(Second2, 5)
End If
End If
'10-60 Minute Filished
End If
'1 -10 Degree Filished
'//////////////////////////////////////////////////////////////////////////////////////1-10 Degree Filished
'/////////////////////////////////////////////////////////////////////////////////////10-100 Degree Starting
If Degree >= 10 And Degree < 100 Then
'10-100 Degree Start
If Minute2 < 1 And Minute2 >= 0 Then
'0-1 Minute Start
'///////处理由于科学计数产生的负数Started
If Minute2 < 0.01667 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 2) & " " & _
" 0" & " " & _
" 0" & _
Left((Val(Degree) - Val(Left(Degree, 2))) * 3600, 3)
End If
If Minute2 >= 0.01667 And Minute2 < 0.1 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 2) & " " & _
" 0" & " " & _
" " & Left((Val(Degree) - Val(Left(Degree, 2))) * 3600, 3)
End If
'///////处理由于科学计数产生的负数Filished
If Second10 < 0.1 And Second10 >= 0 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 2) & " " & _
" 0" & " " & _
" 0" & _
Left(Second10, 3)
End If
If Second10 < 1 And Second10 >= 0.1 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 2) & " " & _
" 0" & " " & _
" 0" & _
Left(Second100, 3)
End If
If Second10 < 10 And Second10 >= 1 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 2) & " " & _
" 0" & " " & _
" " & Left(Second10, 4)
End If
If Second12 < 60 And Second12 >= 10 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 2) & " " & _
" 0" & " " & _
Left(Second12, 5)
End If
End If
'0-1 Minute Filished
If Minute2 < 10 And Minute2 >= 1 Then
'1-10 Minute Start
If Second10 < 0.01 And Second10 >= 0 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 2) & " " & "0" & _
Left((Val(Degree) - Val(Left(Degree, 2))) * 60, 1) & " " & _
" 0" & _
Left(Second10, 3)
End If
If Second10 < 0.1 And Second10 >= 0.01 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 2) & " " & "0" & _
Left((Val(Degree) - Val(Left(Degree, 2))) * 60, 1) & " " & _
" 0" & _
Left(Second10, 3)
End If
If Second10 < 1 And Second10 >= 0.1 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 2) & " " & "0" & _
Left((Val(Degree) - Val(Left(Degree, 2))) * 60, 1) & " " & _
" 0" & _
Left(Second10, 3)
End If
If Second10 < 10 And Second10 >= 1 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 2) & " " & "0" & _
Left((Val(Degree) - Val(Left(Degree, 2))) * 60, 1) & " " & _
" " & Left(Second10, 4)
End If
If Second12 < 60 And Second12 >= 10 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 2) & " " & "0" & _
Left((Val(Degree) - Val(Left(Degree, 2))) * 60, 1) & " " & _
Left(Second12, 5)
End If
End If
'1-10 Minute Filished
If Minute3 < 60 And Minute3 >= 10 Then
'10-60 Minute Start
If Second12 < 0.01 And Second12 >= 0 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 2) & " " & _
Left((Val(Degree) - Val(Left(Degree, 2))) * 60, 2) & " " & _
" 0" & _
Left(Second12, 3)
End If
If Second12 < 0.1 And Second12 >= 0.01 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 2) & " " & _
Left((Val(Degree) - Val(Left(Degree, 2))) * 60, 2) & " " & _
" 0" & _
Left(Second12, 3)
End If
If Second12 < 1 And Second12 >= 0.1 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 2) & " " & _
Left((Val(Degree) - Val(Left(Degree, 2))) * 60, 2) & " " & _
" 0" & _
Left(Second12, 3)
End If
If Second12 < 10 And Second12 >= 1 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 2) & " " & _
Left((Val(Degree) - Val(Left(Degree, 2))) * 60, 2) & " " & _
" " & Left(Second12, 4)
End If
If Second12 < 60 And Second12 >= 10 Then
Sheets(1).Cells(i, 6).Value = " " & Left(Degree, 2) & " " & _
Left((Val(Degree) - Val(Left(Degree, 2))) * 60, 2) & " " & _
Left(Second12, 5)
End If
End If
'10-60 Minute Filished
End If
'10-100 Degree Flished
'/////////////////////////////////////////////////////////////////////////////////////10-100 DegreeFilished
'/////////////////////////////////////////////////////////////////////////////////////100-360 Degree Starting
以上只是部分代码，字数限制啊，后面的你可以参照前面的了。
弱弱的问一句，不会你也是搞测量的吧？哈哈
- 2楼网友：孤独入客枕
- 2021-11-22 13:12
不知道你的数据的规律，没法帮你，发个文件吧追问是这样的，根据一个软件计算出来的角度是255.32124格式的，其实应该是255°32′12.4″.比如A1是255.32124，在A2我想把它转换为弧度。追答我需要知道规律，你的度、分、秒有没有规律，比如度是几位数，分时几位，秒是几位？小数点后一定是4-5位吗？如果是5分，怎么表示，是05还是5？说详细点
- 3楼网友：夜余生
- 2021-11-22 11:41
如果是文本格式的 255°32′12.4″ 用字符串拆分就可以了
或者VBA里有个split函数
就说exlce的拆分吧 =left(A1,find("°",A1)-1) 就可以把255取出来
其他同理 只要有mid 或right就行了
或者VBA里有个split函数
就说exlce的拆分吧 =left(A1,find("°",A1)-1) 就可以把255取出来
其他同理 只要有mid 或right就行了
我要举报
如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！
点此我要举报以上问答信息
大家都在看
推荐资讯