ArcMap 10.4.1地址格式化VBA脚本优化求助

Hey there! Let's tackle those two address formatting headaches you're dealing with—double spaces and duplicate town names. I'll walk you through solutions in both VBA (since you already have some code going) and Python, since you're exploring both languages for this task.

VBA Solution #

Your original code adds spaces regardless of whether a field is empty, which creates those annoying double spaces. Plus, it doesn't check if POST_TOWN matches LOCALITY_N before adding it. Here's a revised VBA expression that fixes both issues:

Trim( _
    IIf(IsNull([SAO_NUM]) Or [SAO_NUM] = "", "", [SAO_NUM] & " ") & _
    IIf(IsNull([SAO_TEXT]) Or [SAO_TEXT] = "", "", [SAO_TEXT] & " ") & _
    IIf(IsNull([PAO_NUM]) Or [PAO_NUM] = "", "", [PAO_NUM] & " ") & _
    IIf(IsNull([PAO_TEXT]) Or [PAO_TEXT] = "", "", [PAO_TEXT] & " ") & _
    IIf(IsNull([STREET_DES]) Or [STREET_DES] = "", "", [STREET_DES] & " ") & _
    IIf(IsNull([LOCALITY_N]) Or [LOCALITY_N] = "", "", [LOCALITY_N] & " ") & _
    IIf(Not IsNull([POST_TOWN]) And [POST_TOWN] <> "" And [POST_TOWN] <> [LOCALITY_N], [POST_TOWN] & " ", "") & _
    "LANCASHIRE " & _
    [POSTCODE] _
)

How this works: #

• No more double spaces: Each IIf statement checks if a field is null or empty. If it is, we add nothing; if not, we add the field value plus a single space.
• No duplicate towns: The POST_TOWN line adds the value only if it's not empty and it doesn't match LOCALITY_N.
• Trim(): Removes any trailing space that might get added at the end of the non-fixed fields.

Testing this with your examples:

1. The double spaces from empty fields will be eliminated, giving you a clean single-space-separated address.
2. For 20 ROSSALL GATE PRESTON PRESTON..., the duplicate PRESTON will be removed since POST_TOWN matches LOCALITY_N.

Python Alternative (Field Calculator) #

Since you're also exploring Python, here's a more readable solution using ArcMap's Field Calculator with the Python parser. First, define this function in the "Pre-Logic Script Code" box:

def format_address(sao_num, sao_text, pao_num, pao_text, street_des, locality_n, post_town, postcode):
    # Start with an empty list to hold non-empty address parts
    address_parts = []
    
    # Add each field to the list only if it has content
    if sao_num:
        address_parts.append(str(sao_num))
    if sao_text:
        address_parts.append(sao_text)
    if pao_num:
        address_parts.append(str(pao_num))
    if pao_text:
        address_parts.append(pao_text)
    if street_des:
        address_parts.append(street_des)
    if locality_n:
        address_parts.append(locality_n)
    
    # Add POST_TOWN only if it's not empty and different from LOCALITY_N
    if post_town and post_town != locality_n:
        address_parts.append(post_town)
    
    # Add fixed county and postcode
    address_parts.append("LANCASHIRE")
    address_parts.append(postcode)
    
    # Join all parts with single spaces (automatically avoids double spaces)
    return " ".join(address_parts)

Then call the function in the field expression box like this:

format_address(!SAO_NUM!, !SAO_TEXT!, !PAO_NUM!, !PAO_TEXT!, !STREET_DES!, !LOCALITY_N!, !POST_TOWN!, !POSTCODE!)

Why this is great: #

• Python's list approach makes it easy to add/remove fields later without messy nested logic.
• join() handles spacing perfectly—no need to worry about trailing spaces or double gaps.
• The code is straightforward to read and debug as you learn Python.

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