Excel单元格信息提取自动化：多词州名解析难题求助

Hey there! I totally get how frustrating it is when your current extraction logic works for single-word states but chokes on ones like "New York" or "New Mexico". Let's break down how to fix this once and for all.

The Root of the Problem #

Your current approach splits on the first space after the comma, which works great for single-word states (like "California"), but fails for multi-word ones because it stops at that first space. Instead of relying on spaces as the delimiter, we can use the zip code as a reliable marker—since zip codes always start with a digit, that's a consistent stopping point for the state name.

Solution 1: Regular Expressions (Clean & Concise) #

Regex is perfect here because we can target the exact pattern: everything after the comma, up to the first digit (the start of the zip code). Here's a Python example you can adapt:

import re

def extract_state(address):
    # Regex pattern: comma -> optional whitespace -> state name -> optional whitespace -> digit (start of zip)
    state_match = re.search(r',\s*(.*?)\s*\d', address)
    if state_match:
        return state_match.group(1).strip()
    # Fallback if address format is unexpected
    return None

# Test with your sample address
sample_address = "Stony Brook, New York 11794-0001 United States"
print(extract_state(sample_address))  # Output: New York

Let's break down the regex to make it clear:

• ,\s*: Matches the comma followed by any number of spaces (handles inconsistent spacing in addresses)
• (.*?): Non-greedy capture group that grabs every character until it hits the next part of the pattern
• \s*\d: Matches any number of spaces followed by a digit (the start of the zip code)

Solution 2: Non-Regex Approach (More Explicit) #

If you prefer avoiding regex, you can split the string and find the first digit manually:

def extract_state(address):
    # Split the address into city section and the rest of the string
    address_parts = address.split(',', 1)
    if len(address_parts) < 2:
        return None  # Address doesn't match expected format
    
    after_city = address_parts[1]
    # Find the index of the first digit (marks the start of the zip code)
    first_digit_idx = next((idx for idx, char in enumerate(after_city) if char.isdigit()), None)
    
    if first_digit_idx is not None:
        # Grab everything before the first digit, then clean up extra whitespace
        state = after_city[:first_digit_idx].strip()
        return state
    return None

# Test it out
sample_address = "Stony Brook, New York 11794-0001 United States"
print(extract_state(sample_address))  # Output: New York

Why This Works #

Both methods leverage the fact that zip codes are always numeric, so they act as a consistent boundary between the state name and the rest of the address. This avoids the problem of splitting on spaces, which breaks for multi-word states.

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