Alpha-sort addresses, ignoring first 1-3 numerals
On May 28, 4:15*pm, smartin wrote:
lalah725 wrote:
I have a huge spreadsheet that I need to sort by road name. *However, the
information is entered as follows: *2 Acorn Drive, 41 Main Street
How can I sort this by alpha, ignoring the numerals preceeding the road name?
This rather arcane formula*, placed in a convenient helper column, will
truncate the address in A2 such that any left-leading characters less
than a capital "A" (in the ASCII sense) are removed. Then you can sort
on it.
=MID(A2,SMALL(IF((CODE(MID(A2,ROW(INDIRECT("1:"&LE N(A2))),1))=65)*(ROW(INDIRECT("1:"&LEN(A2)))),ROW (INDIRECT("1:"&LEN(A2)))),1),99)
This is an array formula, commit by pressing Ctrl+Shift+Enter, not just
Enter.
N.B. "65" is the ASCII value for "A" and "99" is an arbitrarily large
number to return the remaining characters from the address.
|