I have a database with adresses of people. I have to print the database in
order of housenumber. The streetname and housenumber are in the same cell. If
i sort my database on the adress, i get the numbers starting with 1 first.
Like 1, 10, 11, 2, 22, 3, 37. How can i sort the right way?

Sylvester

Sylvester

Having the number and address in the same cell produces text formatted data.

The sort order is correct for this format.

The best way to get around this is to break out the number and address into two
columns and sort on the column with numbers.

Make sure you select all columns before sorting.


Gord Dibben MS Excel MVP

On Mon, 31 Jul 2006 20:15:02 -0700, Sylvester
wrote:

I have a database with adresses of people. I have to print the database in
order of housenumber. The streetname and housenumber are in the same cell. If
i sort my database on the adress, i get the numbers starting with 1 first.
Like 1, 10, 11, 2, 22, 3, 37. How can i sort the right way?

Sylvester

Thank you Gord, but is there an easy way to break up de data? If i use "text
to collums" i can't split text from numbers. I tried using the space as
separation but all i get is a a lot of cells with a piece of the adress.
Sometime a streetname has more then one word and one number. And sometimes
the housenumber has an housenumberextension (like 35a).

Regards,

Sylvester

"Gord Dibben" wrote:

Sylvester

Having the number and address in the same cell produces text formatted data.

The sort order is correct for this format.

The best way to get around this is to break out the number and address into two
columns and sort on the column with numbers.

Make sure you select all columns before sorting.


Gord Dibben MS Excel MVP

On Mon, 31 Jul 2006 20:15:02 -0700, Sylvester
wrote:

I have a database with adresses of people. I have to print the database in
order of housenumber. The streetname and housenumber are in the same cell. If
i sort my database on the adress, i get the numbers starting with 1 first.
Like 1, 10, 11, 2, 22, 3, 37. How can i sort the right way?

Sylvester

Sounds like a nightmare.

Copy the column so's you have two columns the same.

To break out just the numbers try DataText to ColumnsDelimited bySpaceNext.

Select columns other than the numbers column and "do not import".

The only column returned is the number.

Sort on that.

I don't know what you will do with the 35a's

David McRitchie has written macros for parsing street addresses.

http://www.mvps.org/dmcritchie/excel/join.htm#septerm


Gord


On Mon, 31 Jul 2006 20:58:01 -0700, Sylvester
wrote:

Thank you Gord, but is there an easy way to break up de data? If i use "text
to collums" i can't split text from numbers. I tried using the space as
separation but all i get is a a lot of cells with a piece of the adress.
Sometime a streetname has more then one word and one number. And sometimes
the housenumber has an housenumberextension (like 35a).

Regards,

Sylvester

"Gord Dibben" wrote:

Sylvester

Having the number and address in the same cell produces text formatted data.

The sort order is correct for this format.

The best way to get around this is to break out the number and address into two
columns and sort on the column with numbers.

Make sure you select all columns before sorting.


Gord Dibben MS Excel MVP

On Mon, 31 Jul 2006 20:15:02 -0700, Sylvester
wrote:

I have a database with adresses of people. I have to print the database in
order of housenumber. The streetname and housenumber are in the same cell. If
i sort my database on the adress, i get the numbers starting with 1 first.
Like 1, 10, 11, 2, 22, 3, 37. How can i sort the right way?

Sylvester