Extracting Only Street Name from Addresses

[haulinoats]

I am trying to perform an analysis in excel to determine which addresses may potentially the same. I'd like to extract only the street address (without the street suffix) into a new column.

Data:
[TABLE="width: 491"]
<colgroup><col></colgroup><tbody>[TR]
[TD]541 153RD ST[/TD]
[/TR]
[TR]
[TD]541 153RD ST APT 1[/TD]
[/TR]
[TR]
[TD]123 ROOSEVELT AVE # A[/TD]
[/TR]
[TR]
[TD]123 ROOSEVELT AVE[/TD]
[/TR]
[TR]
[TD]123 NW 4TH PL[/TD]
[/TR]
[TR]
[TD]123 NW 4TH PL[/TD]
[/TR]
</tbody>[/TABLE]

Current formulas:

Column B: =MID(S2,FIND(" ",S2)+1,50) - Result: 153RD ST
Column C: =LEFT(T2,FIND(" ",T2)-1) - Final Result: 153rd

Although this works well, my 3rd street address above (123 NW 4th PL) only extracts the NW instead of NW 4TH. I know there is probably a way to extract the entire street name for this scenario, but am struggling to do so.

 

[István Hirsch]

If all the strings have similar structure to the ones you have provided, download into column B the USPS abbreviations of street names (+ full names, if needed), and use the formula in C1:

Excel Workbook
	A	B	C
1	541 153RD ST	AVE	153RD
2	541 153RD ST APT 1	ST	153RD
3	123 ROOSEVELT AVE # A	PL	ROOSEVELT
4	123 ROOSEVELT AVE		ROOSEVELT
5	123 NW 4TH PL		NW 4TH
6	123 NW 4TH PL		NW 4TH
7	555 BOSTON ST		BOSTON
Sheet

 

[haulinoats]

Thanks István. This actually worked perfectly.

Since I'm dealing with thousands of addresses, I found that it'd be easier if I could write a formula that ignores the first street name when it's a direction (E, W, N, S, NW, NE, SE, SW) rather than relying on the USPS list of street suffixes (there are quite a few addresses that may not end with a USPS defined street suffix).

Meaning, in the example "123 NW 4TH PL," the formula will pull "NW 4TH" because it recognizes the 1st word as a directional word.

 

[István Hirsch]

Please show us a table with strings of different kind with the expected result (produced manually) according to the new conception.

 

[haulinoats]

Data:
[TABLE="class: cms_table, width: 491"]
<tbody>[TR]
[TD]541 W 153RD ST[/TD]
[/TR]
[TR]
[TD]541 W 153RD ST APT 1[/TD]
[/TR]
[TR]
[TD]123 E ROOSEVELT AVE # A[/TD]
[/TR]
[TR]
[TD]123 E ROOSEVELT AVE[/TD]
[/TR]
[TR]
[TD]123 NW 4TH PL[/TD]
[/TR]
[TR]
[TD]123 NW 4TH PL

897 SE WASHINGTON ST

123 SD WASHINGTON ST

Find (Directional Fields):
"N, E, W, S, NE, NW, SE, SW"[/TD]
[/TR]
</tbody>[/TABLE]


Result:

[TABLE="class: cms_table, width: 491"]
<tbody>[TR]
[TD]W 153RD[/TD]
[/TR]
[TR]
[TD]E ROOSEVELT[/TD]
[/TR]
[TR]
[TD]E ROOSEVELT[/TD]
[/TR]
[TR]
[TD]NW 4TH[/TD]
[/TR]
[TR]
[TD]NW 4TH

SE WASHINGTON

SD
[/TD]
[/TR]
</tbody>[/TABLE]

 

[István Hirsch]

How "123 SD WASHINGTON ST" became "SD"?

 

[haulinoats]

I wanted to leave SD Washington as SD because I only want to account for directional words.

The directional words I want to look out for are:

N - North
E - East
W - West
S - South
NW - Northwest
NE - Northeast
SW - Southwest
SE - Southeast

Since "SD" isn't a directional word, I am okay with the address only extracting SD rather than "SD Washington."

 

[István Hirsch]

Give this formula a try (confirm with Ctrl -Shift -Enter):

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",200)),200,(1*(MIN(IFERROR(FIND({" NW "," NE "," SW "," SE "," N "," E "," W "," S "},A1),100))<100)+1)*200))

 

[haulinoats]

I thought this one worked, but it looks like it didn't convert it for some:

Example:[TABLE="width: 491"]
<tbody>[TR]
[TD="class: xl66, width: 491"]2411 W BRYN MAWR AVE = "W" instead of "W BRYN"[/TD]
[/TR]
</tbody>[/TABLE]