Numeric sorting of addresses

[KiwiBrian]

How do I sort on a column of addresses so that e.g.:-
1234 567 89 1
Would be sorted as:-
1 89 567 1234

Each entry has the street name following the numeric but these can be
ignored in the sorting criteria.

Many thanks
Brian Tozer

 

[Don Guillett]

The street will not be ignored unless in a separate column
1 aa
1234 ff
567 dd
89 jj

 

[KiwiBrian]

"Don Guillett" wrote

The street will not be ignored unless in a separate column
1 aa
1234 ff
567 dd
89 jj

"KiwiBrian" wrote

Hi Don.
I thank you for your reply but it was a bit too cryptic for me.
Can you enlarge on what you are illustrating please?

In my specific situation, as I have already successfully extracted all
entries for a particular street from the main database, the alpha text in
each case is identical, and only the numeric portion is different.
So I have the first entry as 1 Foo St and the second entry is 1007 Foo St
and many more entries before coming to 2 Foo St.
I hope this clarifies my situation and problem.

Thanks
Brian Tozer

 

[Gord Dibben]

Brian

Have you tried Data>Sort?

Works for me by column or by row if they are set up per your example.

Gord Dibben Excel MVP

 

[Dave Peterson]

Don was saying that if you keep the number and street in the same cell, then
excel will treat the number as text and will sort as he showed.

I'd insert an extra column to the right.
Data|Text to columns
use delimited (by a space)
put the number in that adjacent column
and do not import the other fields.

Then sort on that helper column--that's numeric.

You could also use a formula to pickout that first numeric portion:
=--LEFT(A1,MATCH(TRUE,ISERROR(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0))

 

[Dave Peterson]

Use this formula instead:

=LEFT(A1,MATCH(TRUE,ISERROR(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)-1)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

The first formula depended on that initial space--this formula will find the
first non-numeric entry (and come back one character)