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))