Swapping street names

Question

I have a list of street intersections in excel. Of course it reads S 74th St / Rogers Ave as being different from Rogers Ave / S 74th St. I am trying to swap the cells on the columns so that intersections like that all end up looking the same. I have broken them down into two columns and having been trying the iferror/index/match functions but obviously not doing it right. If there is a macro I could write, that would be ideal. Any ideas?

Answer 1

Assuming your data always appears in a single Cell, in the format "[Street 1] / [Street 2]", this can be done with some helper columns.

First in column B, use the following formula, which will pull out the left name from the intersection:

=LEFT(A1,SEARCH(" / ",A1)-1)

Then do a similar thing in column C:

=RIGHT(A1,LEN(A1)-SEARCH(" / ",A1)-2)

Then, in column D, you will create a new text string showing the intersection, sorted [sort of] alphabetically by the first 4 characters of each road. You can do this as follows:

First, consider the below formula, which picks up the ASCII character value of the first 4 characters of the word found in B1:

=SUM(CODE(MID(LOWER(B1),{1,2,3,4},1)))

This creates a single number which equals the sum of the specific code for each character. We can use that to sort the priority of one cell over another, by comparing with the sum of the same formula for the cell in C1, like so:

=SUM(CODE(MID(LOWER(B1),{1,2,3,4},1)))>SUM(CODE(MID(LOWER(C1),{1,2,3,4},1)))

This will show TRUE if the sum of those codes in B1 is bigger than the sum of those codes in C1. Put this formula in D1 and copy down.

Finally, recreate your ordered string as follows, in column E:

=IF(D1,B1&" / "&C1,C1&" / "&B1)

Now this can be used as a column of ordered data, which should eliminate matches in the streets [assuming no streets have the same 4 characters as any other, and no duplicate streets start differently - ie 5 Ave vs 5th Ave].