Similar cell count?

I am working on an excel spreadsheet for auditing purposes to determine if column A's addresses match any of the addresses from column B. That seemed like a simple formula and I began using conditional formatting for duplicate matches within the columns; however, both columns were imported from different programs that input the addresses differently (i.e. colum A might have 112 Cherry Street while column B has 112 Cherry St. and this prevents excel from identifying them. Even in cases where one column has an extra space, they are not flagged either). The columns are around 24,000 cells each and I wanted a formula that could at least determine st. is similar to street and flag it for me. Am I asking for too much or should I try another program?



Summary (in case I rambled a bit):
The following two cells are for the same address, but are as follows:

112 Cherry Street
112 Cherry St.

I want (if possible) excel to identify these as duplicates and flag them. I would really prefer some automated way instead of manually finding and replacing each abbreviation in the dictionary. Thanks for any help!

Hi taylorrandalls,

Welcome to the forum, you could select columns A & B and go to Find > type in St., click Find ALL, this will show you all the cells meeeting that criteria. Then click Replace, and type in Street, but you would need to be careful with Finding "St." and replacing with "Street", only if there were any Saints, as in St., they would also become "Street".

You could also try something like this, if in A1 you have

112 Cherry Street

and in B1

112 Cherry St.

then in C1

=IF(LEFT(A1,LEN(B1)-1)=LEFT(B1,LEN(B1)-1),"Match","")