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

[Ashish Mathur[_2_]]

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

"lalah725" wrote in message
...
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?

[smartin]

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.

[Harlan Grove[_2_]]

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.

[Harlan Grove[_2_]]

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

[smartin]

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."

[Ashish Mathur[_2_]]

Hi,

You may download the addin from there

http://www.download.com/Morefunc/300...-10423159.html


--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"lalah725" wrote in message
...
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?

[Harlan Grove[_2_]]

"Ashish Mathur" wrote...
You may download an install the xlmorefunc5 addin . . .
....

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)

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)