0

I am working on the 2020 Luca program for the Census.

I have a table in excel with a bunch of addresses but everything is in one column, I need to isolate the fields.

street number, street name, apt number.

for example my addresses look like: "123 North G St 301" or "123 North G St A" (apartment could be a number or letter) So I would need the above address in 3 separate columns

street Num | Street Name | Apt Num
123        | North G St  | 301

I was able to isolate the street numbers, but am stuck on isolating the street name without the apartment number, and isolating the apartment number. one pattern I see is to capture everything after the 4th " " char to get the apartment number but not sure how to do this. and for the street name I need to capture everything after the first " " and everything before the last " " char.

anyone have any ideas?

NULL.Dude
  • 219
  • 1
  • 13
  • is "street" always "st" will road always be "rd" you could maybe just use val(address) to get the number, and then split the string around "rd/st" as needed? – James Lingham Apr 04 '18 at 19:10
  • so you will never have a two word street name without an apartment number: `123 South Saint George St` – Scott Craner Apr 04 '18 at 19:11
  • Use find() to get the position of the last space then return everything to the right (mid() may work), but make sure there are no trailing spaces... sorry on phone so can’t build an example... – Solar Mike Apr 04 '18 at 20:06
  • https://stackoverflow.com/a/18617720 – Vasan Apr 04 '18 at 23:23

1 Answers1

1

Based on your input you have the apt number after the last space. I created two helper columns to find the first and last spaces and put those in B2 and C2 respectively. I then used those to get your three column splits put in D2, E2, and F2. There are a bunch of ways to find the last space, but this one works. You can also combine the formulas and drop the helper columns if that is easier.

assuming your address is in A2

B2=find(" ", A2)
C2=find("$", SUBSTITUTE(A2, " ", "$", len(A2)-len(SUBSTITUTE(A2, " ", ""))))
D2=left(A2, find( " ", A2)-1)
E2=(mid(A2, B2+1, C2-B2))
F2=right(A2, len(A2)-C2)

Address |first space    |last space |   Street Number | Street    |Apt #
123 North G St 301 |4   |15         |123              |North G St |301
123 North G St A   |4   |15         |123              |North G St |A
Rob T
  • 98
  • 1
  • 10