Alpha-sort addresses, ignoring first 1-3 numerals

L

lalah725

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?
 
A

Ashish Mathur

Hi,

You may download an install the xlmorefunc5 addin and then use the following
array formula (Ctrl+Shift+Enter). I am assuming that your range is C3:C4 -
please change as per requirement.

Select range E3:E4 and write the following array formula (Ctrl+Shift+Enter)

=VSORT(MID(C3:C4,SEARCH(" ",C3:C4,1)+1,LEN(C3:C4)-SEARCH("
",C3:C4,1)),MID(C3:C4,SEARCH(" ",C3:C4,1)+1,LEN(C3:C4)-SEARCH("
",C3:C4,1)),1)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
H

Harlan Grove

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:"&LEN(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.
Click to expand...
 
H

Harlan Grove

smartin said:
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.
Click to expand...
....

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

Harlan Grove

Ashish Mathur said:
You may download an install the xlmorefunc5 addin . . .
Click to expand...
....

If there were such an add-in. Google returns no hits searching for
xlmorefuncs. That means your post hasn't made it yet to browser-based
ng archives, but your post and this response would eventually become
the only hits for it.

Do you mean Longre's MOREFUNC.XLL add-in? If so, you should try to use
the correct names of products you suggest. If you really mean
xlmorefuncs, you need to provide a url to where it could be found.
=VSORT(MID(C3:C4,SEARCH(" ",C3:C4,1)+1,LEN(C3:C4)-SEARCH(" ",C3:C4,1)),
MID(C3:C4,SEARCH(" ",C3:C4,1)+1,LEN(C3:C4)-SEARCH(" ",C3:C4,1)),1)
Click to expand...

Looks like you do mean MOREFUNC.XLL. If so, your MID(...) term is
suboptimal. Better to use more functions provided by MOREFUNC.XLL.

=VSORT(C3:C4,REGEX.SUBSTITUTE(C3:C4,"^( *\d+ +)?",""),1)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I sort addresses in excel? 4
Combo Box sort first by alpha-numeric address sets 11
How do I sort a column of street number/street name by the stree. 3
Sorting data bust ignoring numbers 3
how to sort addresses in microsoft excel 2
Drop Down List & VLOOKUP - getting wrong data 5
3 level sort, last special order 4
SQL query question 1

Top