Harlan Grove wrote:
smartin wrote...
...
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.
...

I tested your formula. It works just like you say it does, and
therefore fubars addresses like

333 42nd Street

Far better to find the first space, which involves a much simpler
formula.

=REPLACE(TRIM(A2),1,FIND(" ",TRIM(A2)),"")

On the off chance the OP might have addresses without street numbers,
the formula becomes slightly trickier.

=IF(COUNT(-LEFT(TRIM(A2),1)),REPLACE(TRIM(A2),1,FIND(" ",TRIM
(A2)),""),TRIM(A2))

Aye, alas, my attack will indeed fail with fubars addresses as you say.

Thanks for keeping me honest!

--
Nevertheless, I'm adding this to my compilation of "Array formulas I
never thought I'd figure out and might come in handy."