Sorting addresses by the number
 

I am trying to sort a column of addresses based on the number of the address.
(from lowest number to highest).

But when I do it, all numbers starting with 1 come first- so 1000 High
Street is listed before 5 High Street. I have tried to research this and I
can't find much info... just that it might have to do with numbers being
stored as text. Ive tried changing it and it doesn't work. Help???

Sorting addresses by the number
 

Yeah, addresses are formatted as text, not numbers. You'll notice that
Windows sorts file names in the same way. If you want to sort by the street
number, you have to extract the street number from the address, put that
value into a separate column, and sort on the separate column.

If you have a street address, say 255 W 94th St. NY NY you could use the
Text to Columns function (data--text to columns) to extract the numbers.

Someone may have a more elegant solution than that.

Dave

--
Brevity is the soul of wit.


"Blake" wrote:

I am trying to sort a column of addresses based on the number of the address.
(from lowest number to highest).

But when I do it, all numbers starting with 1 come first- so 1000 High
Street is listed before 5 High Street. I have tried to research this and I
can't find much info... just that it might have to do with numbers being
stored as text. Ive tried changing it and it doesn't work. Help???

Sorting addresses by the number
 

Blake

If the address is all in one cell, then this would be text and Excel is sorting
as text.

The only way to get what you want is to break out the numbers from the address
into separate cells.

You should be able to get the numbers separated by using DataText to Columns
with space as a delimiter.

When sorting, be sure to select all columns and sort by the number column.


Gord Dibben MS Excel MVP

On Mon, 2 Oct 2006 13:03:01 -0700, Blake
wrote:

I am trying to sort a column of addresses based on the number of the address.
(from lowest number to highest).

But when I do it, all numbers starting with 1 come first- so 1000 High
Street is listed before 5 High Street. I have tried to research this and I
can't find much info... just that it might have to do with numbers being
stored as text. Ive tried changing it and it doesn't work. Help???

Sorting addresses by the number
 

assuming there is a space between Numbers and street address
in column X you can type
=LEFT(A1,FIND(" ",A1,1)-1)
this will give you the number of the address as text then you can sort this
column.
go to data--sort--coulmnX--ok--select sort anything looks like a number
as a number--select ok

Nikki
"Dave F" wrote:

Yeah, addresses are formatted as text, not numbers. You'll notice that
Windows sorts file names in the same way. If you want to sort by the street
number, you have to extract the street number from the address, put that
value into a separate column, and sort on the separate column.

If you have a street address, say 255 W 94th St. NY NY you could use the
Text to Columns function (data--text to columns) to extract the numbers.

Someone may have a more elegant solution than that.

Dave

--
Brevity is the soul of wit.


"Blake" wrote:

I am trying to sort a column of addresses based on the number of the address.
(from lowest number to highest).

But when I do it, all numbers starting with 1 come first- so 1000 High
Street is listed before 5 High Street. I have tried to research this and I
can't find much info... just that it might have to do with numbers being
stored as text. Ive tried changing it and it doesn't work. Help???