Comparing addresses in a single column.

[excellahuntress]

Clients tend to write their addresses in different ways. How can I make it so that excel states those addresses are the same/match? I have over 5000 rows to go through.

Example
100 Ab 28th street
1930 Fergy Ave
100 28th st
3216 Broadway Ave
2627 Fate Street
W 100 28th street
1320 Summit Blvd

So all of the 100 28th St addresses would be matches

 

[hnsd24_CN]

union.xlsm
	A	B
1	100 Ab 28th street	same
2	1930 Fergy Ave	different
3	100 28th st	same
4	3216 Broadway Ave	different
5	2627 Fate Street	different
6	W 100 28th street	same
7	1320 Summit Blvd	different
Sheet1

Cell Formulas
Range	Formula
B1:B7	B1	=IF(ISNUMBER(SEARCH("*100*28th*",A1)),"same","different")

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$A$1:$C$10	B1

 

[hnsd24_CN]

Or use AutoFilter

input:
*100*28th*

or Contains

 

[excellahuntress]

Or use AutoFilter
View attachment 25278
input:
*100*28th*
View attachment 25279
or Contains
View attachment 25280

Thank you. My fault for not being specific enough. This isn't the only address on my 5000+ row that is spelled differently. So the numbers and words all vary

 

[hnsd24_CN]

I know that,this is just a way of thinking about the use of wildcards