Excel中经纬度格式化处理

度分秒转十进制

=LEFT(A1,FIND("°",A1)-1)+MID(A1,FIND("°",A1)+1,FIND("′",A1)-FIND("°",A1)-1)/60+MID(A1,FIND("′",A1)+1,FIND("″",A1)-FIND("′",A1)-1)/3600

十进制转度分秒

=INT(A2) & "°" & INT((A2-INT(A2))*60) &"′"  & ROUND(((A2-C2)*60-INT((A2-C2)*60))*60,0) &"″"

 度分秒转固定格式

107°10′47″

1071047E

29°0′56″

290056N

=MID(A2,1,FIND("°",A2)-1)&TEXT(MID(A2,FIND("°",A2)+1,FIND("′",A2)-FIND("°",A2)-1),"00")&TEXT(MID(A2,FIND("′",A2)+1,FIND("″",A2)-FIND("′",A2)-1),"00") & "E"