将格式不规范的度分秒坐标转换为十进制度坐标

问题说明 #

你手头的度分秒坐标存在格式混乱问题：包含中英文混写的引号、多余前缀（如Lat: ）、不规则空格，甚至出现分/秒数值超过60的情况，且所有数字后的0实际应为度数符号°。以下是修正度数符号后的原始数据集：

Latitude Longitude
40°39' 05''N 280°44' 31''E
40°34' 51''N 280°19' 35''E
80°81’ 31.54” N 270°36’ 39.87" E
90°20’ 15.62” N 270°12’ 18.05” E
80°45’ 2.45” N 270°21' 26.38" E
80°74' 82.00" N 270°44' 96" E
80°51’ 31.00” N 270°33’ 41’’ E
40°25’ 27.44’’N 320°31’ 54.85’’E
Lat: 40°27’ 35.62’’N 320°17’ 56.46’’E
40°43' 39''N 280°21' 34''E

解决步骤 #

1. 一键转换公式（Excel 365及以上版本） #

利用LET函数整合清洗与转换逻辑，无需分步处理，直接输出十进制度数。

纬度转换公式（以A2单元格为例）

=LET(
    raw, A2,
    cleaned, TRIM(SUBSTITUTE(SUBSTITUTE(raw,"’","'"),"”","'")),
    cleaned_no_prefix, SUBSTITUTE(cleaned,"Lat: ",""),
    deg_val, VALUE(LEFT(cleaned_no_prefix,FIND(" ",cleaned_no_prefix)-1)),
    min_val, VALUE(MID(cleaned_no_prefix,FIND("'",cleaned_no_prefix)+1,FIND("'",cleaned_no_prefix,FIND("'",cleaned_no_prefix)+1)-FIND("'",cleaned_no_prefix)-1)),
    sec_val, VALUE(MID(cleaned_no_prefix,FIND("'",cleaned_no_prefix,FIND("'",cleaned_no_prefix)+1)+1,FIND(LEFT(RIGHT(cleaned_no_prefix,1)),"NESW")-FIND("'",cleaned_no_prefix,FIND("'",cleaned_no_prefix)+1)-1)),
    dir, RIGHT(cleaned_no_prefix,1),
    decimal_deg, deg_val + min_val/60 + sec_val/3600,
    IF(OR(dir="S",dir="W"),-decimal_deg,decimal_deg)
)

经度转换公式（以B2单元格为例）

将上述公式中的A2替换为B2即可，逻辑完全一致。

2. 公式核心逻辑 #

• 格式清洗：统一全角引号为半角，去除Lat: 前缀，修剪多余空格；
• 数值拆分：通过字符串提取函数分离度、分、秒的数值；
• 十进制计算：按十进制 = 度 + 分/60 + 秒/3600转换，若方向为S/W则取负值（你的数据均为N/E，无需处理负值）；
• 异常兼容：自动处理分/秒超过60的情况（如74分会转换为1度14分，直接计入十进制结果）。

3. 示例转换结果 #

以第一行数据为例：

• 纬度40°39'05''N → 40.65138889°
• 经度280°44'31''E → 280.7419444°

内容的提问来源于stack exchange，提问作者Paula