Excel地址字段拆分求助：2020人口普查LUCA项目街道名与公寓号提取

Hey there! Glad you've already nailed extracting the house number—let's tackle splitting the street name and apartment number next. Based on your examples ("123 North G St 301" and "123 North G St A"), here are reliable Excel formulas tailored to your address formats:

Extract Apartment Number #

Since the apartment number is always the last segment of your address (whether it's a number or letter), use this formula to pull it out:

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))

How it works:

• SUBSTITUTE(A1," ",REPT(" ",LEN(A1))) replaces every single space in the address with a string of spaces equal to the full length of the address.
• RIGHT(...,LEN(A1)) grabs the rightmost characters equal to the address length—this effectively isolates the last segment (all prior segments get pushed far left by the long spaces).
• TRIM() cleans up any leftover extra spaces to give you a clean apartment number.

Extract Street Name #

Assuming your full address is in cell A1 and you already have the house number in cell B1, use this formula to get the street name:

=TRIM(MID(A1,LEN(B1)+2,FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-LEN(B1)-2))

How it works:

• LEN(A1)-LEN(SUBSTITUTE(A1," ","")) counts the total number of spaces in the address.
• SUBSTITUTE(A1," ","@",...) replaces the last space with an @ symbol (we use @ because it's unlikely to appear in standard addresses).
• FIND("@",...) gives the position of that last space, marking where the apartment number starts.
• MID(A1,LEN(B1)+2,...) extracts the text starting right after the house number (and its following space) up to just before the last space.
• TRIM() removes any leading/trailing spaces that might sneak in.

Example Results #

For address A1 = "123 North G St 301":

• House number B1 = 123
• Apartment number D1 = 301 (from the first formula)
• Street name C1 = "North G St" (from the second formula)

For address A1 = "123 North G St A":

• Apartment number D1 = "A"
• Street name C1 = "North G St"

Quick Note #

This works perfectly for your given address formats. If you run into edge cases (like apartment numbers prefixed with "APT" or "#"), you can tweak the formulas to account for those specific patterns—but for your examples, these should do the trick!

内容的提问来源于stack exchange，提问作者NULL.Dude