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:

541 153RD ST

541 153RD ST APT 1

123 ROOSEVELT AVE # A

123 ROOSEVELT AVE

123 NW 4TH PL

123 NW 4TH PL

<colgroup><col></colgroup><tbody>
</tbody>

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:

541 W 153RD ST

541 W 153RD ST APT 1

123 E ROOSEVELT AVE # A

123 E ROOSEVELT AVE

123 NW 4TH PL

123 NW 4TH PL 897 SE WASHINGTON ST 123 SD WASHINGTON ST Find (Directional Fields): "N, E, W, S, NE, NW, SE, SW"

<tbody>
</tbody>

Result:

W 153RD

E ROOSEVELT

E ROOSEVELT

NW 4TH

NW 4TH SE WASHINGTON SD

<tbody>
</tbody>

 

[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:

2411 W BRYN MAWR AVE = "W" instead of "W BRYN"

<tbody>
</tbody>