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.