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?