如何用Google Apps Script/公式按缩写列表格式化地址？

我有一批待格式化的地址样本：

Simple Addresses:

    30 LOCUST AVENUE
    11 QUINE STREET
    1 Avenue A
    737-41 RAMSEY AVENUE STRAVEN
    731 RAMSEY AVE STRAVENUE
    100 HOFFMAN PL THROUGHWAY

需要按照以下缩写规则匹配替换：

Abbreviation List:

    AVENUE     -> AVE
    STREET     -> ST
    STRAVEN    -> STR
    THROUGHWAY -> TRWY
    STRAVENUE  -> STR

期望得到的格式化后地址：

30 LOCUST AVE
11 QUINE STR
1 Ave A
737-41 RAMSEY AVE STR
731 RAMSEY AVE STR
100 HOFFMAN PL TRWY

作为新手，我找不到实现这个需求的公式或脚本，求解决方案。

方案1：Excel公式实现 #

用Excel处理的话，直接用嵌套的SUBSTITUTE函数就行，注意先替换长的关键词（比如先换STRAVENUE再换STRAVEN），避免短词把长词的部分内容提前替换了。

假设原始地址在A列，在B1单元格复制下面的公式，下拉填充到所有行：

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(A1),"STRAVENUE","STR"),"AVENUE","AVE"),"STREET","ST"),"STRAVEN","STR"),"THROUGHWAY","TRWY")

如果想保留原地址的大小写（比如示例里的"Avenue"变成"Ave"），可以用这个更灵活的公式：

=LET(
    addr, A1,
    rules, {"AVENUE","AVE";"STREET","ST";"STRAVEN","STR";"THROUGHWAY","TRWY";"STRAVENUE","STR"},
    result, addr,
    i, 1,
    result, IF(ISNUMBER(SEARCH(INDEX(rules,i,1), addr)), SUBSTITUTE(addr, INDEX(rules,i,1), INDEX(rules,i,2)), result),
    i, 2,
    result, IF(ISNUMBER(SEARCH(INDEX(rules,i,1), addr)), SUBSTITUTE(result, INDEX(rules,i,1), INDEX(rules,i,2)), result),
    i, 3,
    result, IF(ISNUMBER(SEARCH(INDEX(rules,i,1), addr)), SUBSTITUTE(result, INDEX(rules,i,1), INDEX(rules,i,2)), result),
    i, 4,
    result, IF(ISNUMBER(SEARCH(INDEX(rules,i,1), addr)), SUBSTITUTE(result, INDEX(rules,i,1), INDEX(rules,i,2)), result),
    i, 5,
    result, IF(ISNUMBER(SEARCH(INDEX(rules,i,1), addr)), SUBSTITUTE(result, INDEX(rules,i,1), INDEX(rules,i,2)), result),
    result
)

这个公式会逐个匹配规则，保留原地址的大小写样式。

方案2：Python脚本实现 #

如果要处理很多地址，用Python脚本批量处理更高效，照着下面的步骤来：

1. 把地址和缩写规则写进代码里
2. 运行脚本自动替换
3. 直接得到结果

代码示例：

# 先定义缩写规则，长的关键词放前面，防止替换出错
abbreviation_rules = {
    "STRAVENUE": "STR",
    "AVENUE": "AVE",
    "STREET": "ST",
    "STRAVEN": "STR",
    "THROUGHWAY": "TRWY"
}

# 原始地址列表
addresses = [
    "30 LOCUST AVENUE",
    "11 QUINE STREET",
    "1 Avenue A",
    "737-41 RAMSEY AVENUE STRAVEN",
    "731 RAMSEY AVE STRAVENUE",
    "100 HOFFMAN PL THROUGHWAY"
]

# 批量处理地址
formatted_addresses = []
for addr in addresses:
    processed_addr = addr
    # 逐个替换规则里的词，同时保留原大小写
    for full, abbr in abbreviation_rules.items():
        processed_addr = processed_addr.replace(full, abbr).replace(full.title(), abbr.title())
    formatted_addresses.append(processed_addr)

# 输出结果
for addr in formatted_addresses:
    print(addr)

运行这段代码后，就能得到你想要的格式化地址了。

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